Hi everyone,

I've decided to attempt to nail all known bugs in pg_dump for 7.5 :)

So, please send me ALL your known bugs/issues with pg_dump, pg_dumpall 
and pg_restore.  Note that I am NOT interested in feature requests, ONLY 
bugs.

A bug is considered to be an issue in pg_dump that means that when a 
legally arrived at state in your PostgreSQL database, running pg_dump 
and then restoring that dump does not result in an identical state. 
Legally means 'obtained without manual catalog hacking' and 'identical' 
means except for object OIDs.

The current list of known issues (for which I haven't submitted a fix 
yet) that I have are as follows:

* Circular view dependencies (a pretty minor/rare issue...can only be 
"fixed" by banning it in the backend)

* Alter object owner, privileges get a bit messed up.  This is really a 
backend bug, but there might be a pg_dump workaround for it.

* Tablespace that primary key and unique constraint indexes are in are 
not dumped

* If you drop your public schema, a drop command is not issued for it in 
the dump, so when you restore your public schema is back

Does anyone have any others?

Chris


Sun, 18 Jul 2004 14:33:09 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
pg_dump bug fixing
On Sun, Jul 18, 2004 at 14:33:09 +0800,
  Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> 
> * If you drop your public schema, a drop command is not issued for it in 
> the dump, so when you restore your public schema is back

I am not sure that is really a bug. If someone really wants less than
what is in template1, they should be dropping stuff from template1
before recreating the database.

Sun, 18 Jul 2004 06:15:34 -0500
Bruno Wolff III <bruno@wolff.to>
Re: pg_dump bug fixing
> I am not sure that is really a bug. If someone really wants less than
> what is in template1, they should be dropping stuff from template1
> before recreating the database.

No, because pg_dump itself dumps template1's contents...

Chris


Sun, 18 Jul 2004 19:42:09 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
On Sun, Jul 18, 2004 at 19:42:09 +0800,
  Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> >I am not sure that is really a bug. If someone really wants less than
> >what is in template1, they should be dropping stuff from template1
> >before recreating the database.
> 
> No, because pg_dump itself dumps template1's contents...

Are you planning on extending this line of thought to other things in
template1 such as operators?

Sun, 18 Jul 2004 08:29:41 -0500
Bruno Wolff III <bruno@wolff.to>
Re: pg_dump bug fixing
On Sun, 2004-07-18 at 07:42, Christopher Kings-Lynne wrote:
> > I am not sure that is really a bug. If someone really wants less than
> > what is in template1, they should be dropping stuff from template1
> > before recreating the database.
> 
> No, because pg_dump itself dumps template1's contents...

Hmm.

1. Add language handler (say plpgsql) to template1.
2. Create new database
3. Dump database
4. Restore dump, having pg_dump create a new database

Doh.. errors because language handlers are there twice.

You can play this game with tables, sequences, and all sorts of other
things that might be useful in the default template. pg_dump just skips
it (not necessarily bad unless they did an alter table afterward) but
pg_restore doesn't like this at all.


I think what we want is a clean template without all of the extras that
template1 has.

If we dump & restore the public schema and other items that we might be
interested in having, we can have pg_dump use template_clean as a
template for new databases. Template_clean contains none of the things
that pg_dump can dump/restore (like language handlers) but is NOT the
default template.

I've done this myself, removed lots of stuff from template1 after
creating a template_<companyname> that is used for creating new DBs
with. This works great when we remember to specify WITH TEMPLATE.



Sun, 18 Jul 2004 12:29:23 -0400
Rod Taylor <pg@rbt.ca>
Re: pg_dump bug fixing
>>No, because pg_dump itself dumps template1's contents...
> 
> Are you planning on extending this line of thought to other things in
> template1 such as operators?

Ah, I see where you are going with this....

I think that we should treat the public schema specially :)  That's 
because it is much more likely to be dropped and messed with than the 
other system objects, in fact you're encouraged to drop it in the docs.

Chris


Mon, 19 Jul 2004 09:22:21 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
Rod Taylor <pg@rbt.ca> writes:
> I think what we want is a clean template without all of the extras that
> template1 has.

Sounds like a job for ... template0 !

Seriously, this thread would be more convincing if anyone in it betrayed
any knowledge that pg_dump wants you to start from template0 rather than
template1.

			regards, tom lane

Sun, 18 Jul 2004 23:55:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump bug fixing 
> Sounds like a job for ... template0 !
> 
> Seriously, this thread would be more convincing if anyone in it betrayed
> any knowledge that pg_dump wants you to start from template0 rather than
> template1.

What if we made it so that template1 is always restored last?  Won't 
that be an improvement?

Chris


Mon, 19 Jul 2004 16:22:59 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
On Sun, 2004-07-18 at 23:55, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > I think what we want is a clean template without all of the extras that
> > template1 has.
> 
> Sounds like a job for ... template0 !

It doesn't quite work in my case as I've removed items included in
template0 (public schema).


Mon, 19 Jul 2004 08:28:08 -0400
Rod Taylor <pg@rbt.ca>
Re: pg_dump bug fixing
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I think that we should treat the public schema specially :)

We already are to some extent, since pg_dump will dump its comment and
privileges, which it would not do for any other predefined object.
I think this is actually an implementation artifact rather than
something that was explicitly intended at the time, but since no one
has complained about it, it's probably a good thing ;-)

Also, if we're going to apply Fabien's proposed patch to alter the
ownership of the public schema, that's still another way in which the
public schema becomes less like a system-defined object ...

			regards, tom lane

Mon, 19 Jul 2004 10:44:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump bug fixing 
> We already are to some extent, since pg_dump will dump its comment and
> privileges, which it would not do for any other predefined object.
> I think this is actually an implementation artifact rather than
> something that was explicitly intended at the time, but since no one
> has complained about it, it's probably a good thing ;-)

Does that mean your in favour of dumping a DROP SCHEMA public; command 
if they have dropped their public schema?  It's definitely not worth 
doing it for any other "system" object due to upwards compatibility of 
the dump files...

Chris


Tue, 20 Jul 2004 09:20:35 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
On Mon, 2004-07-19 at 21:20, Christopher Kings-Lynne wrote:
> > We already are to some extent, since pg_dump will dump its comment and
> > privileges, which it would not do for any other predefined object.
> > I think this is actually an implementation artifact rather than
> > something that was explicitly intended at the time, but since no one
> > has complained about it, it's probably a good thing ;-)
> 
> Does that mean your in favour of dumping a DROP SCHEMA public; command 
> if they have dropped their public schema?  It's definitely not worth 
> doing it for any other "system" object due to upwards compatibility of 
> the dump files...

Please don't. It would be rather surprising to have stuff disappear from
a database while doing a restore -- especially if it's a CASCADE
operation.

Creating the public schema when needed and leaving it out by default is
preferable.



Tue, 20 Jul 2004 00:34:23 -0400
Rod Taylor <pg@rbt.ca>
Re: pg_dump bug fixing