Thread: Re: create if not exists (CINE)

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

Robert Haas <robertmhaas@gmail.com> writes:
> 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.

The argument was not about whether that is the "plain meaning" of the
phrase; it was about whether that is a safe and useful behavior for a
command to have.  There is a pretty substantial group of people who
think that it would be quite unsafe, which is why we failed to arrive
at a consensus that this is a good thing to implement.
        regards, tom lane

On Tue, May 5, 2009 at 9:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> 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.
>
> The argument was not about whether that is the "plain meaning" of the
> phrase; it was about whether that is a safe and useful behavior for a
> command to have.  There is a pretty substantial group of people who
> think that it would be quite unsafe, which is why we failed to arrive
> at a consensus that this is a good thing to implement.

Who are these people other than you, and did you read the rest of my email?

...Robert

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, May 5, 2009 at 9:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The argument was not about whether that is the "plain meaning" of the
>> phrase; it was about whether that is a safe and useful behavior for a
>> command to have. �There is a pretty substantial group of people who
>> think that it would be quite unsafe, which is why we failed to arrive
>> at a consensus that this is a good thing to implement.

> Who are these people other than you,

In the thread that went into this in most detail
http://archives.postgresql.org//pgsql-hackers/2005-10/msg00632.php
it seemed that wanting CINE was a minority opinion, and in any case
a number of pretty serious issues were raised.

> and did you read the rest of my email?

Yes, I did.  I'm not any more convinced than I was before.  In
particular, the example you give is handled reasonably well without
*any* new features, if one merely ignores "object already exists"
errors.
        regards, tom lane

On Wed, May 6, 2009 at 7:22 AM, Asko Oja <ascoja@gmail.com> wrote:
> It was just yesterday when i wondering why we don't have this feature (i was
> trying to use it and it wasn't there :).
> The group of people who think it's unsafe should not use the feature.
> Clearly this feature would be useful when managing large amounts of servers
> and would simplify our release process.
>
> On Wed, May 6, 2009 at 5:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
[...]
>> Yes, I did.  I'm not any more convinced than I was before.  In
>> particular, the example you give is handled reasonably well without
>> *any* new features, if one merely ignores "object already exists"
>> errors.
>
> It sounds pretty amazing. Ignoring errors as a suggested way to use
> PostgreSQL.
> We run our release scripts inside transactions (with exception of concurrent
> index creation). So if something unexpected happens we are left still in
> working state.
> PostgreSQL ability to do DDL changes inside transaction was one of biggest
> surprises/improvements when switching from Oracle. Now you try to bring us
> down back to the level of Oracle :)

Hm, You can do it easily today with help of PL/PgSQL, say like this:

CREATE OR REPLACE FUNCTION foo_upgrade() RETURNS VOID AS $$
BEGIN BEGIN   CREATE TABLE foo(i int, t text); EXCEPTION   WHEN duplicate_table THEN RAISE NOTICE 'Table foo already
exists';END; BEGIN   ALTER TABLE foo ADD COLUMN t text; EXCEPTION   WHEN duplicate_column THEN RAISE NOTICE 'Column
foo.talready exists'; END; 
END;

...the only drawback is that you need to have PL/PgSQL installed. :-)



Personally I don't like 'CREATE IF NOT EXISTS'.  I find it 'messy'. :-)

What I wish PostgreSQL would have is ability to do "conditional
rollback to savepoint".
This way one could write a PostgreSQL SQL script that would contain conditional
behaviour similar to exceptions handling above.  For instance backend could
handle sort of EXCEPTION clause:

SAVEPOINT create_foo;
CREATE TABLE foo(i int, t text);

START EXCEPTION WHEN duplicate_table; -- if there was duplicate_table exception, all -- commands within this block are
executed.-- if there was no error, all commands are -- ignored, until we reach 'END EXCEPTION;' -- command. ROLLBACK TO
create_foo;ALTER TABLE foo ADD COLUMN t text; 
END EXCEPTION;

...or some \conditional commands at psql client side.

Just my 0.02 :)
  Best regards,       Dawid
--  ..................        ``The essence of real creativity is a certain: *Dawid Kuroczko* :         playfulness, a
flittingfrom idea to idea: qnex42@gmail.com :     without getting bogged down by fixated demands.''`..................'
Sherkaner Underhill, A Deepness in the Sky, V. Vinge 

On Wed, May 6, 2009 at 9:04 AM, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On Wed, May 6, 2009 at 7:22 AM, Asko Oja <ascoja@gmail.com> wrote:
>> It was just yesterday when i wondering why we don't have this feature (i was
>> trying to use it and it wasn't there :).
>> The group of people who think it's unsafe should not use the feature.
>> Clearly this feature would be useful when managing large amounts of servers
>> and would simplify our release process.
>>
>> On Wed, May 6, 2009 at 5:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [...]
>>> Yes, I did.  I'm not any more convinced than I was before.  In
>>> particular, the example you give is handled reasonably well without
>>> *any* new features, if one merely ignores "object already exists"
>>> errors.
>>
>> It sounds pretty amazing. Ignoring errors as a suggested way to use
>> PostgreSQL.
>> We run our release scripts inside transactions (with exception of concurrent
>> index creation). So if something unexpected happens we are left still in
>> working state.
>> PostgreSQL ability to do DDL changes inside transaction was one of biggest
>> surprises/improvements when switching from Oracle. Now you try to bring us
>> down back to the level of Oracle :)
>
> Hm, You can do it easily today with help of PL/PgSQL, say like this:
>
> CREATE OR REPLACE FUNCTION foo_upgrade() RETURNS VOID AS $$
> BEGIN
>  BEGIN
>    CREATE TABLE foo(i int, t text);
>  EXCEPTION
>    WHEN duplicate_table THEN RAISE NOTICE 'Table foo already exists';
>  END;
>  BEGIN
>    ALTER TABLE foo ADD COLUMN t text;
>  EXCEPTION
>    WHEN duplicate_column THEN RAISE NOTICE 'Column foo.t already exists';
>  END;
> END;
>
> ...the only drawback is that you need to have PL/PgSQL installed. :-)

Well, that and it's a lot more code to do the same thing.

> Personally I don't like 'CREATE IF NOT EXISTS'.  I find it 'messy'. :-)
>
> What I wish PostgreSQL would have is ability to do "conditional
> rollback to savepoint".
> This way one could write a PostgreSQL SQL script that would contain conditional
> behaviour similar to exceptions handling above.  For instance backend could
> handle sort of EXCEPTION clause:
>
> SAVEPOINT create_foo;
> CREATE TABLE foo(i int, t text);
>
> START EXCEPTION WHEN duplicate_table;
>  -- if there was duplicate_table exception, all
>  -- commands within this block are executed.
>  -- if there was no error, all commands are
>  -- ignored, until we reach 'END EXCEPTION;'
>  -- command.
>  ROLLBACK TO create_foo;
>  ALTER TABLE foo ADD COLUMN t text;
> END EXCEPTION;
>
> ...or some \conditional commands at psql client side.

I don't think a psql extension is a very good approach, because not
everyone wants to run their SQL via psql (I use DBD::Pg, for example).Sucking some of the functionality of PL/pgsql
intothe main SQL 
engine could be useful (I'm sure it will meet with overwhelming
opposition from someone, though) but if we do I don't see much reason
to imagine the syntax as you've done here.

...Robert

On Wed, May 6, 2009 at 3:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> 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.
>
> The argument was not about whether that is the "plain meaning" of the
> phrase; it was about whether that is a safe and useful behavior for a
> command to have.  There is a pretty substantial group of people who
> think that it would be quite unsafe, which is why we failed to arrive
> at a consensus that this is a good thing to implement.

I need this feature and have a good case.
We (at over-blog/jfg-networks) use slony-1 for replication.

When i create a new materialized view :
1) I create the materialized view with a "create ... as select ..." on
the master node.
2) I create the needed functions and triggers.
3) I create a sql file including : the structure of the table, the
functions, the triggers. I don't want to create the table on slave
node using a "create as select" but using a simple good old create.
(slony need an empty table when subscribing a new table to
replication. Doing this create as select on slave node will stop the
production for a looong time (as slony exclusively lock the whole set
when executing thoses kind of scripts))
4) the standard procedure is to do an "execute script" in slony to
create all thoses thoses table/triggers/etc...
5) slony execute this script on the master and if it doesn't fail, it
execute the scripts on all slaves nodes.

But :
if i do a simple "CREATE", the execution fail on the master because
the table already exist.
I don't want to add a DROP IF EXISTS because it will drop the content
of the materialized view on the master, and will replace it with an
empty regular table.

Solution :
If i had a "CREATE IF NOT EXISTS", the script won't fail on the master
and will execute correctly on all nodes.

Thank you

--
Laurent Laborde
Sysadmin at jfg://networks

On Wed, May 6, 2009 at 9:04 AM, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On Wed, May 6, 2009 at 7:22 AM, Asko Oja <ascoja@gmail.com> wrote:
>> It was just yesterday when i wondering why we don't have this feature (i was
>> trying to use it and it wasn't there :).
>> The group of people who think it's unsafe should not use the feature.
>> Clearly this feature would be useful when managing large amounts of servers
>> and would simplify our release process.
>>
>> On Wed, May 6, 2009 at 5:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [...]
>>> Yes, I did.  I'm not any more convinced than I was before.  In
>>> particular, the example you give is handled reasonably well without
>>> *any* new features, if one merely ignores "object already exists"
>>> errors.
>>
>> It sounds pretty amazing. Ignoring errors as a suggested way to use
>> PostgreSQL.
>> We run our release scripts inside transactions (with exception of concurrent
>> index creation). So if something unexpected happens we are left still in
>> working state.
>> PostgreSQL ability to do DDL changes inside transaction was one of biggest
>> surprises/improvements when switching from Oracle. Now you try to bring us
>> down back to the level of Oracle :)
>
> Hm, You can do it easily today with help of PL/PgSQL, say like this:
>
> CREATE OR REPLACE FUNCTION foo_upgrade() RETURNS VOID AS $$
> BEGIN
>  BEGIN
>    CREATE TABLE foo(i int, t text);
>  EXCEPTION
>    WHEN duplicate_table THEN RAISE NOTICE 'Table foo already exists';

That's kinda like, when parallel parking, determining that it's time
to pull forward when you hit the car behind you.  If you are going
through the trouble of making a function to do schema upgrades, you
would definitely want to query the information schema first to
determine if you needed to create table, add columns, etc.
Subtransactions should be used to handled _unexpected_ errors.

> Personally I don't like 'CREATE IF NOT EXISTS'.  I find it 'messy'. :-)
>
> What I wish PostgreSQL would have is ability to do "conditional
> rollback to savepoint".
> This way one could write a PostgreSQL SQL script that would contain conditional
> behaviour similar to exceptions handling above.  For instance backend could
> handle sort of EXCEPTION clause:

I've griped endlessly about this...I think the 'savepoint' command is
worthless without additional functionality.  In the early drafts of
subtransactions, this wasn't the case...you could push and pop
transactions without using plpgsql.  I don't know how to fix the
current behavior though...maybe:

begin;
savepoint x;
<stuff>
recover;
commit;

Where recover rolls back to last substransaction if there's an error
else its a NOP.  (this idea may have already failed to passed
muster...i've floated several ideas over the years).  With proper
subtransaction support in sql, $SUBJECT wouldn't be necessary, because
we could use the car-smack method (you could make the same case for
drop..if exists which we already have).

merlin