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

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