Re: Patch to add support of "IF NOT EXISTS" to others "CREATE" statements

All,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Jun 12, 2013 at 3:00 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > On 6/12/13 1:29 PM, Fabrízio de Royes Mello wrote:
> >> - CREATE AGGREGATE [ IF NOT EXISTS ] ...
> >> - CREATE CAST [ IF NOT EXISTS ] ...
> >> - CREATE COLLATION [ IF NOT EXISTS ] ...
> >> - CREATE OPERATOR [ IF NOT EXISTS ] ...
> >> - CREATE TEXT SEARCH {PARSER | DICTIONARY | TEMPLATE | CONFIGURATION} [
> >> IF NOT EXISTS ] ...
> >> - CREATE TYPE [ IF NOT EXISTS ] ... [AS [{ENUM | RANGE}] (...)]
> >
> > I'm wondering where "IF NOT EXISTS" and "OR REPLACE" will meet.
>
> I kind of don't see the point of having IF NOT EXISTS for things that
> have OR REPLACE, and am generally in favor of implementing OR REPLACE
> rather than IF NOT EXISTS where possible. The point is usually to get
> the object to a known state, and OR REPLACE will generally accomplish
> that better than IF NOT EXISTS. However, if the object has complex
> structure (like a table that contains data) then "replacing" it is a
> bad plan, so IF NOT EXISTS is really the best you can do - and it's
> still useful, even if it does require more care.

This patch is in the most recent commitfest and marked as Ready for
Committer, so I started reviewing it and came across the above.

I find myself mostly agreeing with the above comments from Robert, but
it doesn't seem like we've really done a comprehensive review of the
various commands to make a 'command' decision on each as to if it should
have IF NOT EXISTS or OR REPLACE options.

The one difficulty that I do see with the 'OR REPLACE' option is when we
can't simply replace an existing object due to dependencies on the
existing definition of that object. Still, if that's the case, wouldn't
you want an error? The 'IF NOT EXISTS' case is the no-error option, but
then you might not know what kind of state the system is in.

Fabrízio, can you clarify the use-case for things like CREATE AGGREGATE
to have IF NOT EXISTS rather than OR REPLACE, or if there is a reason
why both should exist? Complicating our CREATE options is not something
we really wish to do without good reason and we certainly don't want to
add something now that we'll wish to remove in another version or two.

Thanks!

Stephen