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