On Wed, May 6, 2009 at 9:04 AM, Dawid Kuroczko <qne...@gmail.com> wrote: > On Wed, May 6, 2009 at 7:22 AM, Asko Oja <asc...@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 <t...@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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers