Re: Truncate if exists

On 10 October 2012 02:10, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> The second is for making deployment scripts idempotent.  For example,
>> say you have script A which creates table "josh", and script B which
>> needs table "josh" to be empty, if present.  Since the two scripts are
>> tied to different database features, and you don't know which one will
>> be deployed first, it's useful to have TRUNCATE IF EXISTS.  Yes, you can
>> solve that problem with DO, but why make users go to the extra effort?
>
> Hmm.  That's an interesting point.  I think we're currently in
> somewhat of a limbo zone about where we ought to have IF EXISTS and IF
> NOT EXISTS options, and where we should not.  Really, I'd like to
> figure out what policy we want to have, and then go make everything
> work that way.  I don't exactly know what the policy should be, but if
> we don't have one then we're going to have to argue about every patch
> individually, which is already getting to be more than tedious.  At
> the one extreme, you have Tom, who probably would not have added any
> of these given his druthers; at the other extreme, there are probably
> some people who would say we ought to have this for every command in
> the book, right down to INSERT IF EXISTS (and, hey, why not INSERT OR
> CREATE for good measure?).  I'm not sure what the right thing to do
> is... but we should probably come up with some consensus position we
> can all live with, and then go make this uniform[1].

Damn it, now I have an opinion.

I would say two things:

1) Consistency for DDL syntax is important. Sometimes humans still
write SQL and often, ORMs generate SQL. Asking poeple to guess what
our syntax is from release to release is a good way to have people not
bother to support us properly. As Peter says, Truncate is not DDL (and
argument I have used), but it is often used alongside DDL and does
have many of the same characteristics. INSERT IF EXISTS is simply an
argument ad absurdum, not a requirement that needs to be addressed.

2) Clearly, rollout scripts benefit from not throwing errors.
Personally I would prefer setting SET ddl_abort_on_missing_object =
false; at the top of a script than having to go through every SQL
statement and add extra syntax. That might even help people more than
littering SQL with extra clauses.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services