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

Sat, 06 Dec 2003 00:18:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Call for pg_dump testing
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

Sun, 7 Dec 2003 17:58:15 +0300 (MSK)
Oleg Bartunov <oleg@sai.msu.su>
Re: Call for pg_dump testing
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

Sun, 07 Dec 2003 11:22:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Call for pg_dump testing 
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



Mon, 08 Dec 2003 09:36:16 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Call for pg_dump testing
> 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

Sun, 07 Dec 2003 21:56:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Call for pg_dump testing 
> 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


Mon, 08 Dec 2003 11:19:47 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Call for pg_dump testing
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

Sun, 07 Dec 2003 22:43:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Call for pg_dump testing 
> 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


Mon, 08 Dec 2003 12:03:56 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Call for pg_dump testing
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

Sun, 07 Dec 2003 23:31:41 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Call for pg_dump testing 