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

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