Re: create if not exists (CINE)

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. :-)

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 flitting from idea to idea
: qnex42(at)gmail(dot)com : without getting bogged down by fixated demands.''
`..................' Sherkaner Underhill, A Deepness in the Sky, V. Vinge