Re: ALTER TABLE ... IF EXISTS feature?

On Fri, Nov 5, 2010 at 10:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> What you're proposing would maybe be useful for overwriting a database
> that contains portions of what is in the source database, but what's
> the use of that?  You could just as well dropdb and start fresh.  The
> interesting case here is where the target db has *more* objects than
> are in the dump, and I really doubt that there is any self-consistent
> behavior that pg_dump can automatically provide for such cases.  It
> can't drop objects it doesn't know about, and it also has no hope of
> ensuring that their relationships to the replaced objects remain
> consistent.  Silently wiping out FKs, for instance, seems like a pretty
> bad idea.

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, especially considering the default mode is
not to run in a single transaction; a bunch of errors will be spit out
to the log (except in the case where it will, as you say, silently
wipe out a FK), but it'll probably be too late to do anything.

It only complains (and then fails to act anyway, as there is no object
to act upon) when no such object exists. One way to ensure it doesn't
delete a foreign key that makes less sense is to ensure that all
dependents are to-be-dumped DependentObjects in pg_dump.
Pattern-matching/partial pg_dumps have never been sound in the general
case anyway.

> In most cases you'd *better* study those error messages, to see what manual cleanup you're going to need to undertake.

Of what possible use to me is a transaction-aborting error message and
state emitted by an ALTER that is then succeeded by a series of DROP
(TABLE|DOMAIN) statements that encompass the dependents anyway?

I think an error message saying "couldn't drop this table because
other objects that were not named by the cleaning-dump depend on it"
falls into the category of interesting error message that should abort
the transaction. An error condition/xact abort complaining about a
missing constraint for tables that are about to have DROP statements
execute for both of them is considerably less useful.

> So, basically, I've never seen any fully credible use case for pg_dump --clean

Then why is it not on the road to deprecation, or at least given a
strong warning to not use it in the --help message (or is it)? As-is I
think it may only exist to misguide people.

fdr