Re: Truncate if exists

On 10 October 2012 02:10, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus <josh(at)agliodbs(dot)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