Обсуждение: pg_restore --single-transaction and --clean

As another glitch in pg_restore, a combination of options
--single-transaction and --clean raises errors if we restore data
into an empty database. The reason is pg_restore uses DROP <OBJECT>.
The cleanup command fails if the target object doesn't exist.

Is it a TODO item to replace "DROP" into "DROP IF EXISTS"
for cleanup commands in pg_restore?

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> writes:
> Is it a TODO item to replace "DROP" into "DROP IF EXISTS"
> for cleanup commands in pg_restore?

No.  We try to avoid using nonstandard SQL in dumps.
        regards, tom lane

On Wed, Feb 10, 2010 at 10:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> writes:
>> Is it a TODO item to replace "DROP" into "DROP IF EXISTS"
>> for cleanup commands in pg_restore?
>
> No.  We try to avoid using nonstandard SQL in dumps.

How often do we succeed?  It seems unlikely that our dumps would be
restorable into any other database.

...Robert

Robert Haas <robertmhaas@gmail.com> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We try to avoid using nonstandard SQL in dumps.
> 
> How often do we succeed?  It seems unlikely that our dumps would
> be restorable into any other database.
When we were running in a mixed environment we had several occasions
where it was useful to feed pg_dump --column-inserts output into
Sybase databases.  It was very nice to have that.  I think we did
sometimes have to filter it through sed to deal with BOOLEAN vs BIT
issues.
-Kevin

Kevin Grittner escribió:
> Robert Haas <robertmhaas@gmail.com> wrote:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  
> >> We try to avoid using nonstandard SQL in dumps.
> > 
> > How often do we succeed?  It seems unlikely that our dumps would
> > be restorable into any other database.
>  
> When we were running in a mixed environment we had several occasions
> where it was useful to feed pg_dump --column-inserts output into
> Sybase databases.  It was very nice to have that.  I think we did
> sometimes have to filter it through sed to deal with BOOLEAN vs BIT
> issues.

Maybe we should have a --compatible-mode or some such that enables these
things, instead of staying away from useful PG-only features.

The problem would then be how to test it ...

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Kevin Grittner escribi�:
>> Robert Haas <robertmhaas@gmail.com> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> We try to avoid using nonstandard SQL in dumps.

>>> How often do we succeed?  It seems unlikely that our dumps would
>>> be restorable into any other database.

>> When we were running in a mixed environment we had several occasions
>> where it was useful to feed pg_dump --column-inserts output into
>> Sybase databases.  It was very nice to have that.  I think we did
>> sometimes have to filter it through sed to deal with BOOLEAN vs BIT
>> issues.

> Maybe we should have a --compatible-mode or some such that enables these
> things, instead of staying away from useful PG-only features.

Well, the subtext of my comment was really that this case isn't useful
enough to justify introducing a nonstandard construct into dumps.
IMO the whole *point* of --single-transaction is to fail if the database
isn't in the state you thought it was.  If you want to restore into an
empty DB with --single-transaction, don't use --clean.  Problem solved.

--clean has got other issues anyway with a DB that isn't in exactly the
expected state.  If the inter-object dependencies aren't quite what they
were in the source, drops are likely to fail because dependent objects
still remain.  Should we therefore make all pg_dump's drop commands
CASCADE?  I don't think so; the side-effects could be nasty.
        regards, tom lane

Tom Lane escribió:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Kevin Grittner escribi�:
> >> Robert Haas <robertmhaas@gmail.com> wrote:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>>> We try to avoid using nonstandard SQL in dumps.
> 
> >>> How often do we succeed?  It seems unlikely that our dumps would
> >>> be restorable into any other database.
> 
> >> When we were running in a mixed environment we had several occasions
> >> where it was useful to feed pg_dump --column-inserts output into
> >> Sybase databases.  It was very nice to have that.  I think we did
> >> sometimes have to filter it through sed to deal with BOOLEAN vs BIT
> >> issues.
> 
> > Maybe we should have a --compatible-mode or some such that enables these
> > things, instead of staying away from useful PG-only features.
> 
> Well, the subtext of my comment was really that this case isn't useful
> enough to justify introducing a nonstandard construct into dumps.

That's true, but this is not the first time we've left out some feature
from dumps because they would make them standards-incompatible.  If we
have enough of these (and I have no idea that we do), maybe we could
start here.  This is of course just a future TODO item, not something to
consider for 9.0.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.