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