Re: [HACKERS] upgrade path / versioning roles
On Thu, Sep 01, 2005 at 02:59:37PM -0400, Tom Lane wrote:
Patrick Welche [EMAIL PROTECTED] writes:
I tried the fix mentioned in the earlier message to encourage
validation. Now dumping this fixed database, and loadinging it into the
new database gives:
ALTER FUNCTION
psql:./huge.db:4403: ERROR: function plpgsql_validator(oid) does not exist
CREATE FUNCTION
ALTER FUNCTION
psql:./huge.db:4517: ERROR: language plpgsql does not exist
HINT: You need to use createlang to load the language into the database.
Ah, right, *that's* why it's a good idea to have the dependency from the
language to the function ;-) ... else there's no guarantee pg_dump will
dump them in the right order. If you want you could add a suitable
pg_depend row.
:-) Thanks all OK..
One other thing I noticed on this longlived database, is that one can
pg_dump new tables, and their associated sequences will be created,
but for old tables, the associated sequences need creating. The
difference seems to be old:
Table public.meter
id | integer | not null default nextval('meter_id_seq'::text)
new:
Table public.summary
id | integer | not null default nextval('public.summary_id_seq'::text)
that the old sequence hasn't got the schema explicitly appended..
(Just in case someone else comes across this..)
Cheers,
Patrick
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] upgrade path / versioning roles
I think we still recommend using *new* pg_dump to dump *old* server when upgrading. If one tries that with today's pg_dump (8.1beta1) against a 8.1devel server of 6 May, i.e., predating roles, one gets: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: relation pg_catalog.pg_roles does not exist pg_dump: The command was: SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) as rolname, nspacl FROM pg_namespace Cheers, Patrick ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] upgrade path / versioning roles
Patrick Welche [EMAIL PROTECTED] writes: I think we still recommend using *new* pg_dump to dump *old* server when upgrading. If one tries that with today's pg_dump (8.1beta1) against a 8.1devel server of 6 May, i.e., predating roles, one gets: [ shrug... ] The current code is expecting that 8.1 means what it means today. For dumping from a between-releases snapshot, you'd better use the pg_dump from the same snapshot. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] upgrade path / versioning roles
Patrick Welche [EMAIL PROTECTED] writes: I tried the fix mentioned in the earlier message to encourage validation. Now dumping this fixed database, and loadinging it into the new database gives: ALTER FUNCTION psql:./huge.db:4403: ERROR: function plpgsql_validator(oid) does not exist CREATE FUNCTION ALTER FUNCTION psql:./huge.db:4517: ERROR: language plpgsql does not exist HINT: You need to use createlang to load the language into the database. Ah, right, *that's* why it's a good idea to have the dependency from the language to the function ;-) ... else there's no guarantee pg_dump will dump them in the right order. If you want you could add a suitable pg_depend row. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] upgrade path / versioning roles
On Thu, Sep 01, 2005 at 02:31:15PM -0400, Tom Lane wrote: Patrick Welche [EMAIL PROTECTED] writes: I think we still recommend using *new* pg_dump to dump *old* server when upgrading. If one tries that with today's pg_dump (8.1beta1) against a 8.1devel server of 6 May, i.e., predating roles, one gets: [ shrug... ] The current code is expecting that 8.1 means what it means today. For dumping from a between-releases snapshot, you'd better use the pg_dump from the same snapshot. OK - that way around just gets the NOTICE psql:./huge.db:11: NOTICE: SYSID can no longer be specified - fair enough. BTW there was an earlier thread about plpgsql validation. The aforementioned 8.1devel database has a long history and createlang was run against an early 7 incarnation, with pg_dumpall - new database all the way to the present. I tried the fix mentioned in the earlier message to encourage validation. Now dumping this fixed database, and loadinging it into the new database gives: ALTER FUNCTION psql:./huge.db:4403: ERROR: function plpgsql_validator(oid) does not exist CREATE FUNCTION ALTER FUNCTION psql:./huge.db:4517: ERROR: language plpgsql does not exist HINT: You need to use createlang to load the language into the database. now to unfix the fix... Cheers, Patrick ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
