Thread: 7.5-dev, pg_dumpall, dollarquoting

7.5-dev, pg_dumpall, dollarquoting

From
Stefan Kaltenbrunner
Date:
Hi!


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.
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 ?


thanks


Stefan


Re: 7.5-dev, pg_dumpall, dollarquoting

From
Christopher Kings-Lynne
Date:
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



Re: 7.5-dev, pg_dumpall, dollarquoting

From
Tom Lane
Date:
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


Re: 7.5-dev, pg_dumpall, dollarquoting

From
Andrew Dunstan
Date:
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



Re: 7.5-dev, pg_dumpall, dollarquoting

From
Josh Berkus
Date:
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


Re: 7.5-dev, pg_dumpall, dollarquoting

From
Christopher Kings-Lynne
Date:
> 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



Re: 7.5-dev, pg_dumpall, dollarquoting

From
Tom Lane
Date:
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


Re: 7.5-dev, pg_dumpall, dollarquoting

From
Josh Berkus
Date:
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


Re: 7.5-dev, pg_dumpall, dollarquoting

From
Tom Lane
Date:
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


Re: 7.5-dev, pg_dumpall, dollarquoting

From
Christopher Kings-Lynne
Date:
> 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