Re: Re: Patch to add support of "IF NOT EXISTS" to others "CREATE" statements - Mailing list pgsql-hackers

Stephen Frost <sfrost@snowman.net> writes:
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> 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.

There's been pretty extensive theorizing about this in the past (try
searching the pghackers archives for "CINE" and "COR"), and I think the
rough consensus was that it's hard to do COR sensibly for objects
containing persistent state (ie tables) or with separately-declarable
substructure (again, mostly tables, though composite types have some of
the same issues).  However, if COR does make sense then CINE is an
inferior alternative, because of the issue about not knowing the resulting
state of the object for sure.

Given this list I would absolutely reject CINE for aggregates (why in the
world would we make them act differently from functions?), and likewise
for casts, collations, operators, and types.  I don't see any reason not
to prefer COR for these object kinds.  There is room for argument about
the text search stuff, though, because of the fact that some of the text
search object types have separately declarable substructure.

> 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 main knock on COR is that there's no way for the system to completely
protect itself from the possibility that you replaced the object
definition with something that behaves incompatibly.  For instance, if we
had COR for collations and you redefined a collation, that might (or might
not) break indexes whose ordering depends on that collation.  However,
we already bought into that type of risk when we invented COR for
functions, and by and large there have been few complaints about it.
The ability to substitute an improved version of a function seems to be
worth the risks of substituting a broken version.
        regards, tom lane