On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Daniel Farina <drfar...@acm.org> writes: >> I am somewhat sympathetic to this argument, except for one thing: > >> pg_dump --clean will successfully and silently wipe out a foreign key >> right now, should it exist, > > No, it will not, because we don't use CASCADE in the drop commands.
I know it does not use CASCADE, but if I understand it correctly, foreign keys are dropped between tables, and then the tables are dropped. (effectively a manual cascade) In not-single-transaction mode, this can cause a foreign key to get dropped, but then the DROP TABLE could subsequently fail, leaving two tables that were once connected no longer connected. I could be wrong about this, but I think this is the case. If that is not the case, please correct me before continuing to read... If the referencing table is not scoped for dumping by pg_dump, then wouldn't the transaction *correctly* (or perhaps I should say "desirably") abort upon attempting to drop the PK? Right now pg_dump --clean is falling down in very common cases where constraints are being dropped prior to all the related objects being dropped. I will also make an argument that, provided --clean is regarded as a feature to have at all, that ensuring it can be used productively in a well-formed case in single-transaction mode is going to prevent a lot of mistakes. As-is the user is compelled -- should they opt to use clean -- to not use single-transaction mode for restores because it's practically guaranteed to fail all the time should they use foreign key constraints anywhere, unless they are certain that all objects in the database being restored into exist with the same relationships, for sure. Right now to get it to do anything useful it *must* run in multi-transaction mode so constraint drops can fail in their own transaction, and that means that you will be left with a database with some subset of constraints remaining (only discernable by the log). I could be wrong, but I think the change I'm suggesting is very safe, and quite potentially safer because users will be more inclined to restore in a transaction. fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
