Re: ToDo: pg_backup - using a conditional DROP

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Nov 15, 2011 at 10:36 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>>> I'm wondering why we need an option for this, though. Assuming we
>>> make DROP IF EXISTS work anywhere that it doesn't already, why not
>>> just always produce that rather than straight DROP? It seems
>>> categorically better.

>> I think there's a fuzzy idea that we should try to keep our dumps
>> vaguely compatible with other systems. If we add DROP IF EXISTS
>> unconditionally, there would be no way to make them run elsewhere.

> Well, except in --clean mode, we don't emit DROP commands at all. And
> since --clean doesn't even work well on PostgreSQL, I can't get too
> excited about whether it will work everywhere else.

What I find lacking here is a clear explication of what the use-case is;
that is, this proposal seems like a solution in search of a problem.

The default case for pg_dump is that you're going to load a bunch of
objects into an empty database. You don't need any DROP commands,
and this always works fine (or if it doesn't, there's a clear bug to
be fixed in pg_dump).

The --clean switch seems to be targeted at the case that you're trying
to replace the contents of a database that has the same schema as the
one you dumped from. The DROPs will work, more or less, barring nasty
cases such as circular dependencies. (Maybe it will work even then,
but I don't know how carefully we've tested such cases.)

Now, --clean using DROP IF EXISTS would be targeted at, um, what case?
I guess the idea is to be able to load into a database that sort of
mostly shares the same schema as the one you dumped from, only it's not
the same (if it were the same, you'd not need IF EXISTS). The problem
with this is that if the schema isn't the same, it probably hasn't got
the same dependencies, so there's rather little likelihood that pg_dump
will correctly guess what order to issue the DROPs in ... and it
certainly won't know about dependencies to the target objects from other
objects that are in the destination database but weren't in the source.

Possibly you could get around that by ignoring errors; but if you're
willing to ignore errors, you don't need the IF EXISTS qualifiers.

So before buying into this proposal, I want to see a clear demonstration
of a common use-case where it actually does some good. I'm not
convinced that there is one.

regards, tom lane