Re: [HACKERS] pg_dump ordering problem (rc4)
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 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_dump ordering problem (rc4)
Philip Warner [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump ordering problem (rc4)
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 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_dump ordering problem (rc4)
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 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_dump ordering problem (rc4)
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 |/ ---(end of broadcast)--- TIP 3: 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] pg_dump ordering problem (rc4)
Philip Warner [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pg_dump ordering problem (rc4)
Pascal Scheffers [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pg_dump ordering problem (rc4)
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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump ordering problem (rc4)
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 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_dump ordering problem (rc4)
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump ordering problem (rc4)
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 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 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! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_dump ordering problem (rc4)
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 |/ ---(end of broadcast)--- TIP 3: 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
