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

Well, it seems to me there's a mixed signal here.

- When operating with Postgres -> Postgres, the suggestions are stuff like

"Oh, you can just ignore these errors"
"Oh, you can just use sed to change things to play better"

I think I'd rather have *some* option here of having there be some
benefit to "PG->PG". I'd rather hear things like...

"OK, if you're using some other database, you can just ignore these errors"
"OK, if you're using some other database that doesn't know about
DROP IF EXISTS, then you can just use sed to fix that"

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

Back in either 8.1 or 8.2, I think it was, we added in a pretty
complete set of "DROP IF EXISTS" commands.

While I am not keen on adding 250 options to pg_dump, I think it's not
the most wonderful thing in the world to need to anticipate failures.

I'd rather have *some* controls that do NOT involve needing to write a
parser of a fragile combination of SQL as generated by pg_dump.

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

+1 on that, yeah.

The part that'll be nasty-ish is the question of to what degree we'd
like to be cross-PG-version compatible.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"