Thread: pg_dump ordering problem (rc4)
Roberto Mello suggested I post my problem here. He suggested Tom Lane might take a look... I dumped an 7.0.3 database and restored that to rc2, which went fine after a bit of reordering help (It was an OpenACS table set). Now when I dump the same database with rc2 (or 4) I get a different set of ordering problems. Some five functions are used in views before their definitions. In the original (7.0) dump they were in the correct order, but rc2/4 (the only ones I tried) got it wrong. The original OIDs for the functions in the 7.1 dump are lower than those of the views. I do not know what is wrong. I can reproduce the results on another box. I have a copy of the relevant dumps (both the initial 7.0.3 >> 7.1rc2 and the rc4 >> rc4 dump), anyone interested may have them for testing. I compiled on a fairly clean RH6.2/AMD-K6/256M box with nothing more than ./configure; make; make install (so that it ended up in /usr/local/pgsql) The box has the 7.0.3 RPMs installed, 7.1 runs on port 5433 and has a separate postmaster account (postgr71). Regards, Pascal Scheffers. -----BEGIN GEEK CODE BLOCK----- Version: 3.12 GIT$/MU/ED/S/P d(++) s+:+ a?(-) C++ UL++++ P+(--) L+++ E(++) W+++ N++ o? K w++$(---) O- M-- V-- PS@ PE Y+(-) PGP(++) t+@ 5++ X- R tv b++ DI@ D? G e++ h---(-/----) y+++ ------END GEEK CODE BLOCK------ co-hosting is for sissies. get your own machine out there. NOW!
At 18:53 11/04/01 +0200, Pascal Scheffers wrote: > I have a copy >of the relevant dumps (both the initial 7.0.3 >> 7.1rc2 and the rc4 >> rc4 >dump), anyone interested may have them for testing. Yes please. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Pascal Scheffers <pascal@scheffers.net> writes:
> Some five functions are used in views before their definitions. In the
> original (7.0) dump they were in the correct order, but rc2/4 (the only
> ones I tried) got it wrong. The original OIDs for the
> functions in the 7.1 dump are lower than those of the views. I do not know
> what is wrong. I can reproduce the results on another box. I have a copy
> of the relevant dumps (both the initial 7.0.3 >> 7.1rc2 and the rc4 >> rc4
> dump), anyone interested may have them for testing.
Please. Philip Warner would likely want to see them too.
regards, tom lane
Tom, > > dump), anyone interested may have them for testing. > > Please. Philip Warner would likely want to see them too. I don't have his email address... but I am quite willing to send it. Pascal.
I see the problem. Your 7.0.3 dump contains several instances of this
pattern:
CREATE TABLE "users_alertable" ("user_id" int4,"email" character varying(100),"first_names" character
varying(100),"last_name"character varying(100),"password" character varying(30)
);
...
CREATE FUNCTION "user_vacations_kludge" (int4 ) RETURNS int4 AS '
beginreturn count(*) from user_vacations v, users u where u.user_id = $1 and v.user_id = u.user_id and
current_timestampbetween v.start_date and v.end_date;
end;' LANGUAGE 'plpgsql';
...
CREATE RULE "_RETusers_alertable" AS ON SELECT TO users_alertable DO INSTEAD SELECT u.user_id, u.email, u.first_names,
u.last_name,u."password" FROM users u WHERE (((((u.on_vacation_until ISNULL) OR (u.on_vacation_until <
"timestamp"('now'::text)))AND (u.user_state = 'authorized'::"varchar")) AND ((u.email_bouncing_p ISNULL) OR
(u.email_bouncing_p= 'f'::bpchar))) AND (user_vacations_kludge(u.user_id) = 0));
Although this works fine, 7.1 folds the table + rule down into a single
CREATE VIEW, which comes before the CREATE FUNCTION because that's what
the OID ordering suggests will work. Ugh.
A possible kluge answer is to make pg_dump's OID-ordering of views
depend on the OID of the view rule rather than the view relation.
I am not sure if that would break any cases that work now, however.
regards, tom lane
At 17:34 13/04/01 -0400, Tom Lane wrote: > >A possible kluge answer is to make pg_dump's OID-ordering of views >depend on the OID of the view rule rather than the view relation. >I am not sure if that would break any cases that work now, however. > This seems good to me; it should be based on the 'oid of the view', and AFAICT, the rule OID should be it. Should I do this? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
> At 17:34 13/04/01 -0400, Tom Lane wrote: > > > >A possible kluge answer is to make pg_dump's OID-ordering of views > >depend on the OID of the view rule rather than the view relation. > >I am not sure if that would break any cases that work now, however. > > > > This seems good to me; it should be based on the 'oid of the view', and > AFAICT, the rule OID should be it. Should I do this? The view oid is certainly better than the base relation oid. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
At 21:15 13/04/01 -0400, Bruce Momjian wrote: >> At 17:34 13/04/01 -0400, Tom Lane wrote: >> > >> >A possible kluge answer is to make pg_dump's OID-ordering of views >> >depend on the OID of the view rule rather than the view relation. >> >I am not sure if that would break any cases that work now, however. >> > >> >> This seems good to me; it should be based on the 'oid of the view', and >> AFAICT, the rule OID should be it. Should I do this? > >The view oid is certainly better than the base relation oid. > Since I'm in pg_dump at the moment, I'll make the change... ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
>>> > >>> >A possible kluge answer is to make pg_dump's OID-ordering of views >>> >depend on the OID of the view rule rather than the view relation. >>> >I am not sure if that would break any cases that work now, however. >>> > >>> >>> This seems good to me; it should be based on the 'oid of the view', and >>> AFAICT, the rule OID should be it. Should I do this? >> >>The view oid is certainly better than the base relation oid. >> > >Since I'm in pg_dump at the moment, I'll make the change... > Having now looked at pg_dump more closely, I'm not at all sure I want to make the change directly in pg_dump. The reason is that I am trying to move version-specific stuff from pg_dump, and I currently get a view definition by doing 'select pg_getviewdef(<name>)' (rather than looking up the rule etc). Would people mind me adding a 'pg_getviewoid(<name>)' for pg_dump's use? (especially since 7.1 now seems to be out...) ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
> Having now looked at pg_dump more closely, I'm not at all sure I want to
> make the change directly in pg_dump. The reason is that I am trying to move
> version-specific stuff from pg_dump, and I currently get a view definition
> by doing 'select pg_getviewdef(<name>)' (rather than looking up the rule etc).
> Would people mind me adding a 'pg_getviewoid(<name>)' for pg_dump's use?
While that would be a clean solution, it would mean that the problem
will remain until 7.2, because you don't get to assume another initdb
until 7.2. I don't think we want to wait that long; I want to see a fix
of some kind in 7.1.1.
A possible compromise is to do a direct lookup of the OID in 7.1.*
with plans to replace it with some backend-side solution in 7.2 and
beyond.
regards, tom lane
At 03:53 14/04/01 -0400, Tom Lane wrote: > >> Would people mind me adding a 'pg_getviewoid(<name>)' for pg_dump's use? > >While that would be a clean solution, it would mean that the problem >will remain until 7.2, because you don't get to assume another initdb >until 7.2. I don't think we want to wait that long; I want to see a fix >of some kind in 7.1.1. > >A possible compromise is to do a direct lookup of the OID in 7.1.* >with plans to replace it with some backend-side solution in 7.2 and >beyond. > I don't suppose we can change the pg_views view without an initdb? If so, a better solution would be to add a 'view_oid' column. Otherwise, I'll have to kludge pg_dump. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 18:29 14/04/01 +1000, Philip Warner wrote: > >I don't suppose we can change the pg_views view without an initdb? > Having now looked at the source, I realize that initdb is where this view is defined. However, is there any reason that we can not change this definition when upgrading to 7.1.1? The embargo on initdb is, I think, primarily related to avoiding export/import of databases - is that right? If so, then doing non-destructive changes to things like system views does not seem too evil (in this case an update of a row of pg_rewrite and the addition of an attr to pg_views, I think). Am I missing something here? ISTM that the more higher level definitions we have (eg. functions returning multiple rows, DEFINITION SCHEMAs etc), the more we may need to allow changes to be made of things that are *customarily* defined in initdb. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 17:34 13/04/01 -0400, Tom Lane wrote: > >A possible kluge answer is to make pg_dump's OID-ordering of views >depend on the OID of the view rule rather than the view relation. >I am not sure if that would break any cases that work now, however. > Fixed in CVS. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
> I don't suppose we can change the pg_views view without an initdb?
No, not very readily.
Even assuming that we were willing to require dbadmins to run a script
during upgrade (which I wouldn't want to do unless forced to it), it's
not that easy to fix all the occurrences of a system view. Remember
there will be a separate copy in each database, including template0
which you can't even get to. On top of which, DROP VIEW/CREATE VIEW
wouldn't work because the view would then have the wrong OID, and would
look like a user-created object to pg_dump. You'd have to manually
manipulate tuples in pg_class, pg_attribute, pg_rewrite, etc.
Kluging pg_dump is a *lot* cleaner.
I agree with the idea of adding the rule OID as a new column of pg_views
for 7.2, however.
regards, tom lane