Re: create if not exists (CINE)

On Wed, May 6, 2009 at 9:04 AM, Dawid Kuroczko <qnex42(at)gmail(dot)com> wrote:
> On Wed, May 6, 2009 at 7:22 AM, Asko Oja <ascoja(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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 into the 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