Re: Truncate if exists

On Tue, Oct 09, 2012 at 09:10:13PM -0400, Robert Haas 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.

Agreed. I, too, struggle to envision the concrete use case for TRUNCATE IF
EXISTS, but adding IF [NOT] EXISTS to some marginal candidate commands would
not hurt as part of a broad plan.

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

For what it's worth, I'm in that camp of disfavoring all IF [NOT] EXISTS
syntax. I worked on a project that fed idempotent SQL scripts through psql to
migrate schema changes; I used such syntax then and appreciated the keystrokes
saved. But the syntax is a bandage for raw psql input remaining a hostile
environment for implementing the full range of schema changes. Switch to
submitting your SQL from a richer programming environment, and these additions
to core syntax cease to add much.

nm