Re: create if not exists (CINE)

On Wed, May 6, 2009 at 3:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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