Thread: 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.