Re: ALTER TABLE ... IF EXISTS feature?

On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Daniel Farina <drfarina(at)acm(dot)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