Re: ToDo: pg_backup - using a conditional DROP

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.
>
> 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.

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.

> 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.

mysqldump has a --compatible=OTHER_DB_SYSTEM flag (postgresql is one
of the choices). That might not be a crazy way to approach the
problem, though possibly we'd want --compatible=FOO to be a shorthand
for a collection of behaviors that could alternatively be selected
individually via appropriately named long options
(--no-backslash-commands, --no-set-commands, etc.).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company