Thread: bug report: pg_dump does not use CASCADE in DROP
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Preston Landers
Your email address : planders@journyx.com
System Configuration
---------------------
Architecture (example: Intel Pentium) :
Intel Pentium II 500mhz (dual CPU)
Operating System (example: Linux 2.0.26 ELF) :
Linux 2.4.2-2smp (Redhat 7.1)
PostgreSQL version (example: PostgreSQL-7.3):
PostgreSQL-7.4beta2 snapshot (from 2003/8/26.)
Compiler used (example: gcc 2.95.2) :
GCC 2.96
Please enter a FULL description of your problem:
------------------------------------------------
I'm not sure if this is a bug report, feature request, or evidence of my
infirmity, but here it goes:
pg_dump from 7.3+ does not use the CASCADE in the DROP statements (when
the -c clean option is used.)
This is a problem when you are trying to restore the dump back onto
the same site and tables already exist, or perhaps this is just an error
in my understanding of how you perform Postgresql backup and restores.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
Do a pg_dump -c. Restore it back to the same site. The tables will
not be dropped if they have FK constraints or any other dependencies,
resulting in an incorrect restore.
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Simply include the CASCADE option on all DROP TABLE, INDEX, VIEW, and
TRIGGER statements. If you feel this is too dangerous, at least
provide it as a command-line option to pg_dump, so people don't have
to hand-edit their dump files to be able to restore them.
This is a tough one. The CASCADE shouldn't be needed because the clean should be done in an ordering so that dependency is honored. Of course, that doesn't fix problems with mutually-dependent tables. Should we be using CASCADE? Seems that is going to double-drop some tables. --------------------------------------------------------------------------- Preston Landers wrote: > ============================================================================ > POSTGRESQL BUG REPORT TEMPLATE > ============================================================================ > > > Your name : Preston Landers > Your email address : planders@journyx.com > > > System Configuration > --------------------- > Architecture (example: Intel Pentium) : > Intel Pentium II 500mhz (dual CPU) > > Operating System (example: Linux 2.0.26 ELF) : > Linux 2.4.2-2smp (Redhat 7.1) > > PostgreSQL version (example: PostgreSQL-7.3): > PostgreSQL-7.4beta2 snapshot (from 2003/8/26.) > > Compiler used (example: gcc 2.95.2) : > GCC 2.96 > > > Please enter a FULL description of your problem: > ------------------------------------------------ > > I'm not sure if this is a bug report, feature request, or evidence of my > infirmity, but here it goes: > > pg_dump from 7.3+ does not use the CASCADE in the DROP statements (when > the -c clean option is used.) > > This is a problem when you are trying to restore the dump back onto > the same site and tables already exist, or perhaps this is just an error > in my understanding of how you perform Postgresql backup and restores. > > > > Please describe a way to repeat the problem. Please try to provide a > concise reproducible example, if at all possible: > ---------------------------------------------------------------------- > > Do a pg_dump -c. Restore it back to the same site. The tables will > not be dropped if they have FK constraints or any other dependencies, > resulting in an incorrect restore. > > > If you know how this problem might be fixed, list the solution below: > --------------------------------------------------------------------- > > Simply include the CASCADE option on all DROP TABLE, INDEX, VIEW, and > TRIGGER statements. If you feel this is too dangerous, at least > provide it as a command-line option to pg_dump, so people don't have > to hand-edit their dump files to be able to restore them. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Should we be using CASCADE? Seems that is going to double-drop some
> tables.
It kinda scares me too. If you are loading into a database that already
has stuff in it, seems like CASCADE could lead to dropping stuff that is
not part of the dataset being loaded.
If you have no stuff in the database that is not part of the dataset
being loaded, then there's no percentage in individual DROP commands
anyway --- you'd be better off to drop the whole DB, create a new one,
and run the restore without any DROPs. So AFAICS the use of DROP in
restores is intended for reloading part of an existing database.
As such, automatic DROP CASCADEs seem like an excellent foot-gun.
Much safer to do the required drops manually before running restore.
It might be okay as an option in pg_restore, but not as default
behavior.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Should we be using CASCADE? Seems that is going to double-drop some > > tables. > > It kinda scares me too. If you are loading into a database that already > has stuff in it, seems like CASCADE could lead to dropping stuff that is > not part of the dataset being loaded. > > If you have no stuff in the database that is not part of the dataset > being loaded, then there's no percentage in individual DROP commands > anyway --- you'd be better off to drop the whole DB, create a new one, > and run the restore without any DROPs. So AFAICS the use of DROP in > restores is intended for reloading part of an existing database. > As such, automatic DROP CASCADEs seem like an excellent foot-gun. > Much safer to do the required drops manually before running restore. > > It might be okay as an option in pg_restore, but not as default > behavior. Once pg_dump starts using the dependency information, it seems it could do the drops in the proper order, and when it detects mutually-dependent tables, it can use a single DROP CASCADE to remove them all --- seems like that is a TODO. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Once pg_dump starts using the dependency information, it seems it could
> do the drops in the proper order, and when it detects
> mutually-dependent tables, it can use a single DROP CASCADE to remove
> them all --- seems like that is a TODO.
You missed my point entirely. What if DROP CASCADE causes a drop of a
table that did not even exist in the source database, but was added in
the target after the initial data load? It seems unlikely that that is
desirable behavior for pg_restore.
The correct use of dependency information would be to sort the DROPs
into an order that should succeed *without* CASCADE. (This will
actually happen for free AIUI, once pg_dump uses dependency info fully.
DROPping in the reverse of a safe creation order should work.)
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Once pg_dump starts using the dependency information, it seems it could > > do the drops in the proper order, and when it detects > > mutually-dependent tables, it can use a single DROP CASCADE to remove > > them all --- seems like that is a TODO. > > You missed my point entirely. What if DROP CASCADE causes a drop of a > table that did not even exist in the source database, but was added in > the target after the initial data load? It seems unlikely that that is > desirable behavior for pg_restore. > > The correct use of dependency information would be to sort the DROPs > into an order that should succeed *without* CASCADE. (This will > actually happen for free AIUI, once pg_dump uses dependency info fully. > DROPping in the reverse of a safe creation order should work.) Right, but how do you drop two tables that REFERENCE each other? Seems you have to use CASCADE in that case. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> The correct use of dependency information would be to sort the DROPs
>> into an order that should succeed *without* CASCADE. (This will
>> actually happen for free AIUI, once pg_dump uses dependency info fully.
>> DROPping in the reverse of a safe creation order should work.)
> Right, but how do you drop two tables that REFERENCE each other? Seems
> you have to use CASCADE in that case.
Nope. It's still the inverse problem of pg_dump. pg_dump would have to
dump such a construction with CREATE TABLEs followed by ALTER TABLE ADD
FOREIGN KEYs, right? So the DROPs issued in reverse order are ALTER
TABLE DROP CONSTRAINTs followed by DROP TABLE.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> The correct use of dependency information would be to sort the DROPs > >> into an order that should succeed *without* CASCADE. (This will > >> actually happen for free AIUI, once pg_dump uses dependency info fully. > >> DROPping in the reverse of a safe creation order should work.) > > > Right, but how do you drop two tables that REFERENCE each other? Seems > > you have to use CASCADE in that case. > > Nope. It's still the inverse problem of pg_dump. pg_dump would have to > dump such a construction with CREATE TABLEs followed by ALTER TABLE ADD > FOREIGN KEYs, right? So the DROPs issued in reverse order are ALTER > TABLE DROP CONSTRAINTs followed by DROP TABLE. Yep. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Added to TODO:
* Have pg_dump -c clear the database using dependency
information
---------------------------------------------------------------------------
Preston Landers wrote:
> ============================================================================
> POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
>
> Your name : Preston Landers
> Your email address : planders@journyx.com
>
>
> System Configuration
> ---------------------
> Architecture (example: Intel Pentium) :
> Intel Pentium II 500mhz (dual CPU)
>
> Operating System (example: Linux 2.0.26 ELF) :
> Linux 2.4.2-2smp (Redhat 7.1)
>
> PostgreSQL version (example: PostgreSQL-7.3):
> PostgreSQL-7.4beta2 snapshot (from 2003/8/26.)
>
> Compiler used (example: gcc 2.95.2) :
> GCC 2.96
>
>
> Please enter a FULL description of your problem:
> ------------------------------------------------
>
> I'm not sure if this is a bug report, feature request, or evidence of my
> infirmity, but here it goes:
>
> pg_dump from 7.3+ does not use the CASCADE in the DROP statements (when
> the -c clean option is used.)
>
> This is a problem when you are trying to restore the dump back onto
> the same site and tables already exist, or perhaps this is just an error
> in my understanding of how you perform Postgresql backup and restores.
>
>
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>
> Do a pg_dump -c. Restore it back to the same site. The tables will
> not be dropped if they have FK constraints or any other dependencies,
> resulting in an incorrect restore.
>
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
>
> Simply include the CASCADE option on all DROP TABLE, INDEX, VIEW, and
> TRIGGER statements. If you feel this is too dangerous, at least
> provide it as a command-line option to pg_dump, so people don't have
> to hand-edit their dump files to be able to restore them.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073