Обсуждение: Savepoint or begin

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

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