Thread: Call for pg_dump testing
I have committed some fairly wide-ranging revisions to pg_dump to make it dump database objects in a "safe" order according to the dependency information available from pg_depend. While I know that I have fixed a lot of previously-broken cases, it's hardly unlikely that I've broken some things too. Please give it a try if you can. CVS-tip pg_dump should be fully compatible with 7.4 installations, and reasonably compatible with 7.3 servers as well (but its output is unlikely to load into pre-7.3 servers because of syntax changes). If anyone wants to try using this pg_dump to dump from 7.2 or before and reload into 7.4-or-CVS-tip, I'd also be interested to hear how well that works. When dumping from a pre-7.3 server there is no pg_depend info available, so pg_dump is on its own to guess at a reasonable dump order. I do not promise that pg_dump is any better than before in this situation, but if it's gotten worse then I'd like to know about it. regards, tom lane
On Sat, 6 Dec 2003, Tom Lane wrote: > I have committed some fairly wide-ranging revisions to pg_dump to make > it dump database objects in a "safe" order according to the dependency > information available from pg_depend. While I know that I have fixed > a lot of previously-broken cases, it's hardly unlikely that I've broken > some things too. Please give it a try if you can. CVS-tip pg_dump > should be fully compatible with 7.4 installations, and reasonably > compatible with 7.3 servers as well (but its output is unlikely to > load into pre-7.3 servers because of syntax changes). I tried with 7.3.3 and got error: ./pg_dump: relocation error: ./pg_dump: undefined symbol: get_progname pg@eol:~/add/cvs/pgsql/src/bin/pg_dump$ ldd ./pg_dump libpq.so.3 => /usr/local/pgsql/lib/libpq.so.3 (0x40016000) libz.so.1 => /usr/lib/libz.so.1 (0x40031000) libreadline.so.3 => /usr/lib/libreadline.so.3 (0x40040000) libcrypt.so.1=> /lib/libcrypt.so.1 (0x40062000) libresolv.so.2 => /lib/libresolv.so.2 (0x4008f000) libnsl.so.1=> /lib/libnsl.so.1 (0x400a0000) libdl.so.2 => /lib/libdl.so.2 (0x400b5000) libm.so.6 => /lib/libm.so.6(0x400b8000) libc.so.6 => /lib/libc.so.6 (0x400db000) libtermcap.so.2 => /lib/libtermcap.so.2 (0x401fc000) /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes: > I tried with 7.3.3 and got error: > ./pg_dump: relocation error: ./pg_dump: undefined symbol: get_progname You have a problem with linking to the wrong version of libpq.so. regards, tom lane
Hey Tom, > I have committed some fairly wide-ranging revisions to pg_dump to make > it dump database objects in a "safe" order according to the dependency > information available from pg_depend. While I know that I have fixed > a lot of previously-broken cases, it's hardly unlikely that I've broken > some things too. Please give it a try if you can. CVS-tip pg_dump > should be fully compatible with 7.4 installations, and reasonably > compatible with 7.3 servers as well (but its output is unlikely to > load into pre-7.3 servers because of syntax changes). Awesome effort! I'm interested to know how you deal with circular dependencies in Views and Functions? Also, what happens if I delete a key dependency from my pg_depend table manually? Chris
> I'm interested to know how you deal with circular dependencies in Views > and Functions? There's not currently any code for that, though I imagine we could invent some at need. Please provide example cases. > Also, what happens if I delete a key dependency from my pg_depend table > manually? Postgres has always allowed you to shoot yourself in the foot by manually diddling the system catalogs. I place this in the "if it hurts, don't do it" category ... regards, tom lane
> There's not currently any code for that, though I imagine we could > invent some at need. Please provide example cases. create view v1 as select 1; create view v2 as select 1 + (select * from v1); create or replace view v1 as select * from v2; It seems to me that the only way to solve that one is to dump 'view shells'. eg. for text columns return '' and numeric columns return 0: eg: create view v1 as select 0::integer; create view v2 as select 1 + (select * from v1); create or replace view v1 as select * from v2; >>Also, what happens if I delete a key dependency from my pg_depend table >>manually? > > > Postgres has always allowed you to shoot yourself in the foot by > manually diddling the system catalogs. I place this in the "if it > hurts, don't do it" category ... Is there any reason for us to still allow that? What is there left that requires manual twiddling? Also shouldn't we really separate out the 'can modify catalogs manually' privilege from the 'superuser' privilege? That way dbas could make people superusers who couldn't to extremely bad things to the catalogs? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: >> Please provide example cases. > create view v1 as select 1; > create view v2 as select 1 + (select * from v1); > create or replace view v1 as select * from v2; > It seems to me that the only way to solve that one is to dump 'view > shells'. Hm. As of CVS tip, what you'll get is a complaint along the lines of $ pg_dump circle >outfile pg_dump: [sorter] WARNING: could not resolve dependency loop among these items: pg_dump: [sorter] TABLE v1 (ID 1111 OID 920137) pg_dump: [sorter] RULE _RETURN (ID 1174 OID 920139) pg_dump: [sorter] TABLE v2 (ID 1112 OID 920140) pg_dump: [sorter] RULE _RETURN (ID 1175 OID 920142) and a dump that orders the two views arbitrarily. We can certainly add code to do something different, but are there any real-world cases where this is needed? The above example seems more than slightly made-up. The views aren't actually functional anyway (trying to use either would result in an "infinite recursion" error). Can you show me a non-broken situation where pg_dump needs to resort to view shells? >> Postgres has always allowed you to shoot yourself in the foot by >> manually diddling the system catalogs. I place this in the "if it >> hurts, don't do it" category ... > Is there any reason for us to still allow that? What is there left that > requires manual twiddling? Getting out of unpleasant situations, perhaps. I would very much resist any attempt to forbid that --- we're a long way from being so certain of ourselves as to say that no one should ever hack the catalogs. > Also shouldn't we really separate out the 'can modify catalogs manually' > privilege from the 'superuser' privilege? See pg_shadow.usecatupd. This could stand to be better supported maybe (like with ALTER USER support)? regards, tom lane
> and a dump that orders the two views arbitrarily. We can certainly add > code to do something different, but are there any real-world cases where > this is needed? The above example seems more than slightly made-up. > The views aren't actually functional anyway (trying to use either would > result in an "infinite recursion" error). Can you show me a non-broken > situation where pg_dump needs to resort to view shells? Well then shouldn't we just ban you from creating a view that creates a circular dependency? Thinks... How about if the views were using each others 'table type' to do something? Although you cannot change the return type definition can you? Hmmm. >>Also shouldn't we really separate out the 'can modify catalogs manually' >>privilege from the 'superuser' privilege? > > > See pg_shadow.usecatupd. This could stand to be better supported maybe > (like with ALTER USER support)? Sounds like this should be a TODO... Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: >> Can you show me a non-broken >> situation where pg_dump needs to resort to view shells? > Well then shouldn't we just ban you from creating a view that creates a > circular dependency? I dunno. My question is exactly about whether there are any real useful cases for this. regards, tom lane