Re: ToDo: pg_backup - using a conditional DROP

Excerpts from Robert Haas's message of mar nov 15 11:59:17 -0300 2011:
> On Tue, Nov 15, 2011 at 9:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> >> there is a request on enhancing of pg_backup to produce a conditional
> >> DROPs. A reason for this request is more simple usage in very dynamic
> >> production - cloud BI solution.
> >
> >> pg_backup can have a new option "--conditional-drops" and then pg_dump
> >> will produce a DROP IF EXISTS statements instead DROP statements.
> >
> > That is not going to be possible unless we commit to having an IF EXISTS
> > option for every type of DROP statement, now and in the future.
> > Even then, it's not apparent to me that it solves any real use-case.
> > You're probably better off just using --clean and ignoring any errors.
>
> Ignoring errors sucks, though, because sometimes you want the whole
> thing to succeed or fail as a unit.
>
> 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.

Of course, our dumps already fail for a lot of reasons (for example SET
commands and COPY), but I think if you dump with inserts and COPY and
have the other server ignore errors found while processing the script,
the idea is that you should find that mostly it loads the tables and
data. I don't know how well this principle works for the DROP commands.

I wonder if that instead of trying to remain "somewhat compatible" to
other systems we should instead have a mode specifically designed for
that --one which didn't output SET or backslash commands, used inserts
rather than COPY, etc-- and have the noncompatible mode offer nice
features such as DROP IF EXISTS and the like.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support