From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Savepoint or begin |
Date: | 2011-10-03 09:23:12 |
Message-ID: | 201110031223.12449.achill@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Anssi Kääriäinen | 2011-10-03 09:36:58 | Savepoint or begin |
Previous Message | 姜头 | 2011-10-03 09:03:22 | How can i get record by data block not by sql? |