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.