You might scrap all BEGIN/COMMIT/ROLLBACK stmts, and run your upgrade_all.sql as psql --single-transaction -f upgrade_all.sql Στις Monday 03 October 2011 12:36:58 ο/η Anssi Kääriäinen έγραψε: > I am having the following problem: I have upgrade scripts which are > runnable one-by-one. I will also want to run all of them together. Example: > > table1.sql: > begin; > alter table table1 add column new_col1; > alter table table1 add column new_col2; > commit; > > table2.sql: > begin; > alter table table2 add column new_col1; > alter table table2 add column new_col2; > commit; > > upgrade_all.sql: > begin; > \i table1.sql > \i table2.sql > commit; > > If I run upgrade_all.sql, it will not be atomic, as table1.sql's COMMIT > will commit half of the work and table2.sql's COMMIT will commit another > half of the work. If there is an error when running table2.sql, this > would commit half of the work and rollback half of the work. What I > would like to do is something like: > table1.sql: > savepoint or begin s1; > ... > commit s1; > > If run outside transaction, this would be equivalent to table1.sql, that > is SAVEPOINT OR BEGIN would create a new transaction, and COMMIT s1 > would commit it. If run inside a transaction, this would create a > savepoint and commit would not do anything. The syntax could of course > be much better, but I hope this is enough to show what I am after. > > Is this doable already somehow? Am I doing my upgrade script structuring > wrong? > > - Anssi Kääriäinen > -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
