Re: create if not exists (CINE) - Mailing list pgsql-hackers

On Tue, May 5, 2009 at 11:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, May 5, 2009 at 8:56 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>>> We have debated CREATE ... IF NOT EXISTS in the past, and there is no
>>> consensus on what it should do, so we don't have it for any command. That is
>>> quite a different case from what's being asked for, and the two should not
>>> be conflated.
>
>> I must be missing something, because the semantics of CREATE ... IF
>> NOT EXISTS seem pretty well-defined to me,
>
> Please go read the prior threads (I think searching for "CINE" might
> help, because we pretty shortly started abbreviating it like that).

OK - done, and thanks for the search tip.

I still stand by my original comment.  I think there is no semantic
question about what CREATE IF NOT EXISTS ought to do.  It ought to
create the object if it doesn't exist.  Otherwise, it ought to do
nothing.  That leads to two questions, the first of which Andrew asked
in an email earlier today, and the second of which you asked in the
previous discussion of this issue:

1. Why should it do nothing if the object already exists (as opposed
to any other alternative)?

Answer: Because that's what "CREATE IF NOT EXISTS" means when
interpreted as English.  If you wanted it to take some action when the
object already exists, you'd have to call the command something like
"CREATE IF NOT EXISTS OTHERWISE MUTILATE".  Actually, we pretty much
already have this in the form of "CREATE OR REPLACE", but "CREATE OR
REPLACE" is only suitable for objects whose state can be fully defined
by the command which creates them.  This is true for views and
functions, but false for tables and sequences, which contain user
data.

2. What good is this anyway?

Answer: It's good for schema management.  Typically, you have a
development system and N>0 production systems.  Periodically, you do
releases from develepment to production.  When you release to a
machine X, you want to upgrade that machine from whatever version of
the schema it has now to the one appropriate to the version of the
application you are releasing.  So suppose you have a table caled foo
that didn't exist in version 1 of the software.  In version 2 it was
added with columns id and name.  In version 3 of the software a date
column called bar was added.  You are releasing version 3.  So you
write the following SQL script:

CREATE TABLE IF NOT EXISTS foo (id serial, name varchar not null,
primary key (id));
ALTER TABLE foo ADD COLUMN IF NOT EXISTS bar date;

Observe that after running this script on EITHER a V1 or a V2
database, you now have the V3 schema.  Without CINE, you have to
either write separate upgrade scripts for V1->V3 and V2->V3, or write
a PL/pgsql function that scrutinizes the system catalogs and figures
out what needs to be done, or have some sort of bookkeeping system to
keep track of which DDL bits have previously been executed, or
something other alternative that will definitely be more complicated
than the above.  Obviously, there are more complex cases that CINE
can't handle, but this is actually enough for a pretty good percentage
of them in my experience.  You typically add a table, then as releases
go by you add more columns, then possibly at some point you decide
that whole table was a stupid idea and you rip it out (which is
already well-handled via DROP IF EXISTS).  Typically when adding a
column to an existing table you either allow nulls or set a default,
either of which will work fine with this syntax.  If you need to do
something more complicated (like compute the initial values of bar
based on the contents of some other table), well, then you're back to
where you always are today.

It seems to me that the right thing to do is to support CREATE OR
REPLACE for as many object types as possible.  But that won't be
possible for things like tables unless we can make PostgreSQL
AI-complete, so for those I think we ought to support CINE to cater to
the design pattern above.  That is of course only my opinion, but I
gather from some of the comments made earlier today that I'm not the
only one who wrestles with this problem.

...Robert