7.5-dev, pg_dumpall, dollarquoting

Hi Stefan,

since we have a lot of databases here that suffer from pg_dump's
deficits in 7.3 and 7.4 regarding dependencies, we tried pg_dump from
the upcoming 7.5 release.

Would you be able to specify exactly the deficiences? It's my mission
at the moment to make pg_dump 7.5 known-issue free :)

This version works much better, but it is not possible to dump a
complete cluster using pg_dumpall in a 7.3 or 7.4 compatible way because
pg_dumpall lacks the "-X disable-dollar-quoting" switch.
Would it be possible to modify pg_dumpall to accept the same commands as
pg_dump (at least those that make sense) - or am I missing something here ?

I can add it - do the other hackers want it?

The main problem stefan is that 7.5 dump is seriously only guaranteed to
restore to 7.5. You can use the 7.5 dump to upgrade to 7.5, but not to
restore to 7.4.

At the moment, dollar quoting is your main problem, but there's also the
set default_with_oids stuff, plus I'm also hopefully soon going to fix
the dumping owners issue, which will most likely make pg_dump 7.5 less
likely to restore to 7.4...

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

pg_dumpall lacks the "-X disable-dollar-quoting" switch.

I can add it - do the other hackers want it?

It should be there --- in general pg_dumpall should be able to pass down
any pg_dump switch that makes sense.

regards, tom lane

Tom Lane wrote:

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

pg_dumpall lacks the "-X disable-dollar-quoting" switch.

I can add it - do the other hackers want it?

It should be there --- in general pg_dumpall should be able to pass down
any pg_dump switch that makes sense.

Perhaps rather than replicating every pg_dump option pg_dumpall needs a
single option that can encapsulate them?

e.g.
--dump-options="--foo --bar --baz"

Just a thought

cheers

andrew

KL-

Would you be able to specify exactly the deficiences? It's my mission
at the moment to make pg_dump 7.5 known-issue free :)

Well, since you asked:
(please excuse me if I'm covering old ground. I was off Hackers for almost a
month this spring)

1) When pg_dump 7.4.1 (I have not tested on CVS) pulls a dump from a 7.2
database with confusing dependancies (e.g. functions depend on views which
depend on multiple tables and other views containing other functions), some
objects (almost always functions) still get silently dropped from the dump
file. This "silent dropping" was also a problem in 7.3 (pulling from 7.2),
but nobody wanted to work on it -- especially as it's only possible to
demonstrate with a sufficiently complex early 7.2 database.
I have a good test database for this, I will test with CVS.

2) pg_restore needs to be more tolerant with certain kinds of errors. For
example, if an object already exists in the target database, due to being
from template1, it should be possible to tell pg_restore to ignore the error
with a switch. Currently, this issue prevents me from using pg_restore on
some systems, where the restore isn't run as the superuser. Another switch,
telling pg_restore to attempt to ignore all errors and restore anyway, would
also be keen (though I can see potential abuse issues).
Has this already been addressed in CVS?

--
Josh Berkus
Aglio Database Solutions
San Francisco

1) When pg_dump 7.4.1 (I have not tested on CVS) pulls a dump from a 7.2
database with confusing dependancies (e.g. functions depend on views which
depend on multiple tables and other views containing other functions), some
objects (almost always functions) still get silently dropped from the dump
file. This "silent dropping" was also a problem in 7.3 (pulling from 7.2),
but nobody wanted to work on it -- especially as it's only possible to
demonstrate with a sufficiently complex early 7.2 database.
I have a good test database for this, I will test with CVS.

Nothing gets silently dropped. It will cause an ERROR on creation and
then keep going, but it won't silently drop it... (unless it's a
binary dump thing...)

Anyway, pg_dump in CVS does correct dump ordering based on a topological
sort of the pg_depend relation (thank Tom for that). It will of course
only work on a 7.3 or higher backend.

2) pg_restore needs to be more tolerant with certain kinds of errors. For
example, if an object already exists in the target database, due to being
from template1, it should be possible to tell pg_restore to ignore the error
with a switch. Currently, this issue prevents me from using pg_restore on
some systems, where the restore isn't run as the superuser. Another switch,
telling pg_restore to attempt to ignore all errors and restore anyway, would
also be keen (though I can see potential abuse issues).
Has this already been addressed in CVS?

Hmmm, dunno about this - it wasn't on my radar really. I'll experiment
with it, but I don't think I'm going to have time before June 30th :(

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

2) pg_restore needs to be more tolerant with certain kinds of errors.

Hmmm, dunno about this - it wasn't on my radar really. I'll experiment
with it, but I don't think I'm going to have time before June 30th :(

I think we dealt with this already: pg_restore has been taught that the
correct response to a SQL command error is to report it and forge ahead,
not curl up and die.

regards, tom lane

Chris,

Nothing gets silently dropped. It will cause an ERROR on creation and
then keep going, but it won't silently drop it... (unless it's a
binary dump thing...)

Silently dropped on dump, not on restore.

Anyway, pg_dump in CVS does correct dump ordering based on a topological
sort of the pg_depend relation (thank Tom for that). It will of course
only work on a 7.3 or higher backend.

Right. The issue is really complex databases which were developed in 7.2 or
7.1, which is missing the pg_depends information. In that case, pg_dump
seems to get confused about dependency sorting, and a few objects with long
dependency chains simply don't get backed up.

I'll see if I can put together a nice demo for this.

My concern in reporting this is that there are still a lot of commercial users
out there running 7.2, and I'd like to minimize upgrade pain.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

Right. The issue is really complex databases which were developed in 7.2 or
7.1, which is missing the pg_depends information. In that case, pg_dump
seems to get confused about dependency sorting, and a few objects with long
dependency chains simply don't get backed up.

That would be a flat-out bug. Can we see a test case?

regards, tom lane

It should be there --- in general pg_dumpall should be able to pass down
any pg_dump switch that makes sense.

Do the -S and -O switches also make sense? Should I add them?

Chris