pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedA transaction in transaction? Possible?
18+ messages / 11 participants
[nested] [flat]
* A transaction in transaction? Possible?
@ 2004-11-09 08:47 Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 13:55 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-09 16:20 ` Re: A transaction in transaction? Possible? Bricklen <bricklen@zyahoo.zcomz>
0 siblings, 3 replies; 18+ messages in thread
From: Andrei Bintintan @ 2004-11-09 08:47 UTC (permalink / raw)
To: pgsql-sql
Is it possible to have another transatction in a transaction??? In the following example the last ROLLBACK is totally ignored(transaction1).
//connect to database
$database = dbConnect($dbhost, $dbuser, $dbpass, $dbname);
dbExec($database, "BEGIN"); //transaction1
//*
dbExec($database, "BEGIN");//transaction2
$sql = "UPDATE orders SET technikernotiz='51' WHERE id=16143";
dbExec($database, $sql);
dbExec($database, "COMMIT");//transaction2
/**/
$sql = "UPDATE orders SET reklamationsdetail='51' WHERE id=16143";
dbExec($database, $sql);
dbExec($database, "ROLLBACK");//transaction1
dbClose($database);
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
@ 2004-11-09 13:55 ` Michael Fuhr <mike@fuhr.org>
2 siblings, 0 replies; 18+ messages in thread
From: Michael Fuhr @ 2004-11-09 13:55 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql
On Tue, Nov 09, 2004 at 10:47:06AM +0200, Andrei Bintintan wrote:
> Is it possible to have another transatction in a transaction???
PostgreSQL 8.0 (currently in beta) has savepoints, so you'll be
able to do this:
BEGIN;
UPDATE orders SET technikernotiz='51' WHERE id=16143;
SAVEPOINT foo;
UPDATE orders SET reklamationsdetail='51' WHERE id=16143;
ROLLBACK TO foo;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
@ 2004-11-09 15:24 ` Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2 siblings, 1 reply; 18+ messages in thread
From: Theodore Petrosky @ 2004-11-09 15:24 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql
I thought nested transactions are available in the new
release (8) coming up.
Ted
--- Andrei Bintintan <klodoma@ar-sd.net> wrote:
> Is it possible to have another transatction in a
> transaction??? In the following example the last
> ROLLBACK is totally ignored(transaction1).
>
> //connect to database
> $database = dbConnect($dbhost, $dbuser, $dbpass,
> $dbname);
> dbExec($database, "BEGIN"); //transaction1
>
> //*
> dbExec($database, "BEGIN");//transaction2
> $sql = "UPDATE orders SET
> technikernotiz='51' WHERE id=16143";
> dbExec($database, $sql);
> dbExec($database, "COMMIT");//transaction2
> /**/
>
> $sql = "UPDATE orders SET
> reklamationsdetail='51' WHERE id=16143";
> dbExec($database, $sql);
> dbExec($database, "ROLLBACK");//transaction1
>
> dbClose($database);
>
>
>
__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
@ 2004-11-10 06:23 ` sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 18+ messages in thread
From: sad @ 2004-11-10 06:23 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql
On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote:
> I thought nested transactions are available in the new
> release (8) coming up.
how to commit/rollback them ?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
@ 2004-11-10 06:25 ` Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
0 siblings, 1 reply; 18+ messages in thread
From: Michael Fuhr @ 2004-11-10 06:25 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: Theodore Petrosky <tedpet5@yahoo.com>; Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql
On Wed, Nov 10, 2004 at 09:23:02AM +0300, sad wrote:
> On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote:
> > I thought nested transactions are available in the new
> > release (8) coming up.
>
> how to commit/rollback them ?
CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
BEGIN;
INSERT INTO person (name) VALUES ('Tom');
SAVEPOINT foo;
INSERT INTO person (name) VALUES ('Dick');
ROLLBACK TO foo;
INSERT INTO person (name) VALUES ('Harry');
COMMIT;
SELECT * FROM person;
id | name
----+-------
1 | Tom
3 | Harry
(2 rows)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
@ 2004-11-10 08:45 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 18+ messages in thread
From: Riccardo G. Facchini @ 2004-11-10 08:45 UTC (permalink / raw)
To: pgsql-sql; +Cc: Theodore Petrosky <tedpet5@yahoo.com>; Andrei Bintintan <klodoma@ar-sd.net>; Michael Fuhr <mike@fuhr.org>; sad <sad@bankir.ru>
Sorry, but I understand that your example is not really about nested
transactions, but about sequential transactions.
so, the primary question remains:
how to commit/rollback them ?
--- Michael Fuhr <__> wrote:
> On Wed, Nov 10, 2004 at 09:23:02AM +0300, sad wrote:
> > On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote:
> > > I thought nested transactions are available in the new
> > > release (8) coming up.
> >
> > how to commit/rollback them ?
>
> CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
>
> BEGIN;
> INSERT INTO person (name) VALUES ('Tom');
> SAVEPOINT foo;
> INSERT INTO person (name) VALUES ('Dick');
> ROLLBACK TO foo;
> INSERT INTO person (name) VALUES ('Harry');
> COMMIT;
>
> SELECT * FROM person;
> id | name
> ----+-------
> 1 | Tom
> 3 | Harry
> (2 rows)
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
@ 2004-11-10 09:53 ` Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 18+ messages in thread
From: Michael Fuhr @ 2004-11-10 09:53 UTC (permalink / raw)
To: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; +Cc: pgsql-sql@postgresql.org, Theodore Petrosky <tedpet5@yahoo.com>; Andrei Bintintan <klodoma@ar-sd.net>; sad <sad@bankir.ru>
On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
> Sorry, but I understand that your example is not really about nested
> transactions, but about sequential transactions.
Here's a more elaborate example. If this doesn't demonstrate the
capability you're looking for, then please provide an example of
what you'd like to do and describe the desired behavior.
CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
BEGIN;
INSERT INTO person (name) VALUES ('Alice');
SAVEPOINT s1;
INSERT INTO person (name) VALUES ('Bob');
SAVEPOINT s2;
INSERT INTO person (name) VALUES ('Charles');
SAVEPOINT s3;
INSERT INTO person (name) VALUES ('David');
ROLLBACK TO s3;
INSERT INTO person (name) VALUES ('Edward');
ROLLBACK TO s2;
INSERT INTO person (name) VALUES ('Frank');
RELEASE s1;
INSERT INTO person (name) VALUES ('George');
COMMIT;
SELECT * FROM person;
id | name
----+--------
1 | Alice
2 | Bob
6 | Frank
7 | George
If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
id | name
----+---------
1 | Alice
2 | Bob
3 | Charles
5 | Edward
6 | Frank
7 | George
If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
id | name
----+--------
1 | Alice
7 | George
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
@ 2004-11-10 10:58 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 10:37 ` Re: A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-10 15:23 ` Re: A transaction in transaction? Possible? Peter Eisentraut <peter_e@gmx.net>
2004-11-10 17:28 ` Re: A transaction in transaction? Possible? Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 08:10 ` Re: A transaction in transaction? Possible? Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 4 replies; 18+ messages in thread
From: Achilleus Mantzios @ 2004-11-10 10:58 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; pgsql-sql; Theodore Petrosky <tedpet5@yahoo.com>; Andrei Bintintan <klodoma@ar-sd.net>; sad <sad@bankir.ru>
O Michael Fuhr Ýãñáøå óôéò Nov 10, 2004 :
> On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
>
> > Sorry, but I understand that your example is not really about nested
> > transactions, but about sequential transactions.
>
> Here's a more elaborate example. If this doesn't demonstrate the
> capability you're looking for, then please provide an example of
> what you'd like to do and describe the desired behavior.
>
> CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
>
> BEGIN;
> INSERT INTO person (name) VALUES ('Alice');
>
> SAVEPOINT s1;
> INSERT INTO person (name) VALUES ('Bob');
>
> SAVEPOINT s2;
> INSERT INTO person (name) VALUES ('Charles');
>
> SAVEPOINT s3;
> INSERT INTO person (name) VALUES ('David');
> ROLLBACK TO s3;
>
> INSERT INTO person (name) VALUES ('Edward');
> ROLLBACK TO s2;
>
> INSERT INTO person (name) VALUES ('Frank');
> RELEASE s1;
>
> INSERT INTO person (name) VALUES ('George');
> COMMIT;
Just a very naive thought....
Wouldn't make more sense to allow nested begin/commit/rollback blocks?
>
> SELECT * FROM person;
> id | name
> ----+--------
> 1 | Alice
> 2 | Bob
> 6 | Frank
> 7 | George
>
> If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
>
> id | name
> ----+---------
> 1 | Alice
> 2 | Bob
> 3 | Charles
> 5 | Edward
> 6 | Frank
> 7 | George
>
> If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
>
> id | name
> ----+--------
> 1 | Alice
> 7 | George
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-11-10 10:37 ` Andrei Bintintan <klodoma@ar-sd.net>
3 siblings, 0 replies; 18+ messages in thread
From: Andrei Bintintan @ 2004-11-10 10:37 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Michael Fuhr <mike@fuhr.org>; +Cc: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; pgsql-sql; Theodore Petrosky <tedpet5@yahoo.com>; sad <sad@bankir.ru>
Okay, I see you're speaking about pgsql 8.0
What about 7.4?
Andy.
----- Original Message -----
From: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>
To: "Michael Fuhr" <mike@fuhr.org>
Cc: "Riccardo G. Facchini" <abief_ag_-postgresql@yahoo.com>;
<pgsql-sql@postgresql.org>; "Theodore Petrosky" <tedpet5@yahoo.com>; "Andrei
Bintintan" <klodoma@ar-sd.net>; "sad" <sad@bankir.ru>
Sent: Wednesday, November 10, 2004 12:58 PM
Subject: Re: [SQL] A transaction in transaction? Possible?
> O Michael Fuhr Ýãñáøå óôéò Nov 10, 2004 :
>
> > On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
> >
> > > Sorry, but I understand that your example is not really about nested
> > > transactions, but about sequential transactions.
> >
> > Here's a more elaborate example. If this doesn't demonstrate the
> > capability you're looking for, then please provide an example of
> > what you'd like to do and describe the desired behavior.
> >
> > CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
> >
> > BEGIN;
> > INSERT INTO person (name) VALUES ('Alice');
> >
> > SAVEPOINT s1;
> > INSERT INTO person (name) VALUES ('Bob');
> >
> > SAVEPOINT s2;
> > INSERT INTO person (name) VALUES ('Charles');
> >
> > SAVEPOINT s3;
> > INSERT INTO person (name) VALUES ('David');
> > ROLLBACK TO s3;
> >
> > INSERT INTO person (name) VALUES ('Edward');
> > ROLLBACK TO s2;
> >
> > INSERT INTO person (name) VALUES ('Frank');
> > RELEASE s1;
> >
> > INSERT INTO person (name) VALUES ('George');
> > COMMIT;
>
> Just a very naive thought....
> Wouldn't make more sense to allow nested begin/commit/rollback blocks?
>
> >
> > SELECT * FROM person;
> > id | name
> > ----+--------
> > 1 | Alice
> > 2 | Bob
> > 6 | Frank
> > 7 | George
> >
> > If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
> >
> > id | name
> > ----+---------
> > 1 | Alice
> > 2 | Bob
> > 3 | Charles
> > 5 | Edward
> > 6 | Frank
> > 7 | George
> >
> > If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
> >
> > id | name
> > ----+--------
> > 1 | Alice
> > 7 | George
> >
> >
>
> --
> -Achilleus
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-11-10 15:23 ` Peter Eisentraut <peter_e@gmx.net>
2004-11-10 16:01 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
3 siblings, 1 reply; 18+ messages in thread
From: Peter Eisentraut @ 2004-11-10 15:23 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Michael Fuhr <mike@fuhr.org>; +Cc: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; pgsql-sql; Theodore Petrosky <tedpet5@yahoo.com>; Andrei Bintintan <klodoma@ar-sd.net>; sad <sad@bankir.ru>
Achilleus Mantzios wrote:
> Wouldn't make more sense to allow nested begin/commit/rollback
> blocks?
Possibly. But that consideration would have been more relevant about 6
years ago when they wrote the SAVEPOINT syntax into the SQL standard.
:)
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 15:23 ` Re: A transaction in transaction? Possible? Peter Eisentraut <peter_e@gmx.net>
@ 2004-11-10 16:01 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 16:09 ` Re: A transaction in transaction? Possible? Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 18+ messages in thread
From: Achilleus Mantzios @ 2004-11-10 16:01 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Michael Fuhr <mike@fuhr.org>; Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; pgsql-sql; Theodore Petrosky <tedpet5@yahoo.com>; Andrei Bintintan <klodoma@ar-sd.net>; sad <sad@bankir.ru>
O Peter Eisentraut Ýãñáøå óôéò Nov 10, 2004 :
> Achilleus Mantzios wrote:
> > Wouldn't make more sense to allow nested begin/commit/rollback
> > blocks?
>
> Possibly. But that consideration would have been more relevant about 6
> years ago when they wrote the SAVEPOINT syntax into the SQL standard.
> :)
In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK;
can be replaced with
SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively.
If only transactions weren't a requirement for SAVEPOINTs,
what would we then need BEGIN; COMMIT; ROLLBACK; for?
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 15:23 ` Re: A transaction in transaction? Possible? Peter Eisentraut <peter_e@gmx.net>
2004-11-10 16:01 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-11-10 16:09 ` Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 18+ messages in thread
From: Peter Eisentraut @ 2004-11-10 16:09 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
Achilleus Mantzios wrote:
> In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK;
> can be replaced with
> SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively.
>
> If only transactions weren't a requirement for SAVEPOINTs,
> what would we then need BEGIN; COMMIT; ROLLBACK; for?
Note that under the current arrangement, it doesn't make much sense to
"commit" a subtransaction. It will be committed anyway when the main
transactions commits, and you cannot commit it earlier because the main
transaction could still roll back. So savepoint blocks are not really
transactions, but more like semi-transactions.
In other nested transaction models, things can be different. If you
have so-called open nested transactions, which expose their results to
other transactions already before the parent transaction commits, then
a subtransaction commit is useful. But that behavior violates the
isolation criterion of transactions and therefore needs additional
facilities to behave tolerably.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-11-10 17:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-10 21:36 ` Re: A transaction in transaction? Possible? Stefan Weiss <spaceman@foo.at>
3 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2004-11-10 17:28 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Michael Fuhr <mike@fuhr.org>; Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; pgsql-sql@postgresql.org, Theodore Petrosky <tedpet5@yahoo.com>; Andrei Bintintan <klodoma@ar-sd.net>; sad <sad@bankir.ru>
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> Just a very naive thought....
> Wouldn't make more sense to allow nested begin/commit/rollback blocks?
We actually had it working that way initially, but changed to the
spec-defined behavior, because (a) it wasn't standard, and (b) it
was confusing. See the pghackers archives.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 17:28 ` Re: A transaction in transaction? Possible? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-11-10 21:36 ` Stefan Weiss <spaceman@foo.at>
2004-11-11 08:23 ` Re: A transaction in transaction? Possible? Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 1 reply; 18+ messages in thread
From: Stefan Weiss @ 2004-11-10 21:36 UTC (permalink / raw)
To: pgsql-sql
On Wednesday, 10 November 2004 18:28, Tom Lane wrote:
> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > Just a very naive thought....
> > Wouldn't make more sense to allow nested begin/commit/rollback blocks?
>
> We actually had it working that way initially, but changed to the
> spec-defined behavior, because (a) it wasn't standard, and (b) it
> was confusing. See the pghackers archives.
We used to run into problems with nested transactions in scenarios
like this:
Imagine a database where you have a table for customers, and
each customer can have (in a seperate table) several contacts; a
contact can have one or more addresses, phone numbers, etc. These
tables are connected by foreign keys, but without "on delete"
triggers.
The frontend application has a function for deleting a contact,
which works something like this:
* begin transaction
* delete the contact's addresses, phone numbers, etc
* ...
* delete the contact record itself
* commit
Then there is a function for deleting a customer:
* begin transaction
* for all contacts, call the "delete contact" function
* ...
* delete the customer record itself
* commit
At the moment the application is "simulating" support for nested
transactions: We use a wrapper for the BEGIN and COMMIT calls,
and an internal counter, which is incremented for each BEGIN.
Only the first BEGIN gets sent to the backend. When COMMIT has
been called as many times as BEGIN, we send a real commit (errors
and ROLLBACK are handled too, of course).
It's not perfect, but it does what we need. Savepoints are a nice
feature, but I don't think they could help us here.
cheers,
stefan
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 17:28 ` Re: A transaction in transaction? Possible? Tom Lane <tgl@sss.pgh.pa.us>
2004-11-10 21:36 ` Re: A transaction in transaction? Possible? Stefan Weiss <spaceman@foo.at>
@ 2004-11-11 08:23 ` Gaetano Mendola <mendola@bigfoot.com>
2004-11-11 19:31 ` Re: A transaction in transaction? Possible? Stefan Weiss <spaceman@foo.at>
0 siblings, 1 reply; 18+ messages in thread
From: Gaetano Mendola @ 2004-11-11 08:23 UTC (permalink / raw)
To: Stefan Weiss <spaceman@foo.at>
Stefan Weiss wrote:
> On Wednesday, 10 November 2004 18:28, Tom Lane wrote:
>
>>Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
>>
>>>Just a very naive thought....
>>>Wouldn't make more sense to allow nested begin/commit/rollback blocks?
>>
>>We actually had it working that way initially, but changed to the
>>spec-defined behavior, because (a) it wasn't standard, and (b) it
>>was confusing. See the pghackers archives.
>
>
> We used to run into problems with nested transactions in scenarios
> like this:
>
> Imagine a database where you have a table for customers, and
> each customer can have (in a seperate table) several contacts; a
> contact can have one or more addresses, phone numbers, etc. These
> tables are connected by foreign keys, but without "on delete"
> triggers.
Why "without" ? Are you looking to solve a problem introduced by
yourself ?
> The frontend application has a function for deleting a contact,
> which works something like this:
>
> * begin transaction
> * delete the contact's addresses, phone numbers, etc
> * ...
> * delete the contact record itself
> * commit
>
> Then there is a function for deleting a customer:
>
> * begin transaction
> * for all contacts, call the "delete contact" function
> * ...
> * delete the customer record itself
> * commit
>
> At the moment the application is "simulating" support for nested
> transactions: We use a wrapper for the BEGIN and COMMIT calls,
> and an internal counter, which is incremented for each BEGIN.
> Only the first BEGIN gets sent to the backend. When COMMIT has
> been called as many times as BEGIN, we send a real commit (errors
> and ROLLBACK are handled too, of course).
>
> It's not perfect, but it does what we need. Savepoints are a nice
> feature, but I don't think they could help us here.
You can handle this task using the new functionality introduced with
savepoint: the exception. For more information look at:
http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPP...
Your delete customer can do:
* BEGIN
* for all contacts call delete contact
* ...
* EXCEPTION
* handle your exception
* END;
*
* delete the customer record itself
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 17:28 ` Re: A transaction in transaction? Possible? Tom Lane <tgl@sss.pgh.pa.us>
2004-11-10 21:36 ` Re: A transaction in transaction? Possible? Stefan Weiss <spaceman@foo.at>
2004-11-11 08:23 ` Re: A transaction in transaction? Possible? Gaetano Mendola <mendola@bigfoot.com>
@ 2004-11-11 19:31 ` Stefan Weiss <spaceman@foo.at>
0 siblings, 0 replies; 18+ messages in thread
From: Stefan Weiss @ 2004-11-11 19:31 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-sql
On Thursday, 11 November 2004 09:23, Gaetano Mendola wrote:
> Stefan Weiss wrote:
> > These tables are connected by foreign keys, but without "on delete"
> > triggers.
>
> Why "without" ? Are you looking to solve a problem introduced by
> yourself ?
There are numerous checks involved before a customer (or a contact)
can be deleted, and not all of these checks can be done by querying
the database. Sometimes triggers aren't enough.
> You can handle this task using the new functionality introduced with
> savepoint: the exception. For more information look at:
> http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures
>.html#PLPGSQL-ERROR-TRAPPING
The "delete contact" and "delete customer" functions are not stored
procedures, so I'm not sure how this can help.
thanks,
stefan
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 15:24 ` Re: A transaction in transaction? Possible? Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` Re: A transaction in transaction? Possible? sad <sad@bankir.ru>
2004-11-10 06:25 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Re: A transaction in transaction? Possible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Re: A transaction in transaction? Possible? Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Re: A transaction in transaction? Possible? Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-11-11 08:10 ` Gaetano Mendola <mendola@bigfoot.com>
3 siblings, 0 replies; 18+ messages in thread
From: Gaetano Mendola @ 2004-11-11 08:10 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Achilleus Mantzios wrote:
> O Michael Fuhr Ýãñáøå óôéò Nov 10, 2004 :
>
>
>>On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
>>
>>
>>>Sorry, but I understand that your example is not really about nested
>>>transactions, but about sequential transactions.
>>
>>Here's a more elaborate example. If this doesn't demonstrate the
>>capability you're looking for, then please provide an example of
>>what you'd like to do and describe the desired behavior.
>>
>>CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
>>
>>BEGIN;
>> INSERT INTO person (name) VALUES ('Alice');
>>
>> SAVEPOINT s1;
>> INSERT INTO person (name) VALUES ('Bob');
>>
>> SAVEPOINT s2;
>> INSERT INTO person (name) VALUES ('Charles');
>>
>> SAVEPOINT s3;
>> INSERT INTO person (name) VALUES ('David');
>> ROLLBACK TO s3;
>>
>> INSERT INTO person (name) VALUES ('Edward');
>> ROLLBACK TO s2;
>>
>> INSERT INTO person (name) VALUES ('Frank');
>> RELEASE s1;
>>
>> INSERT INTO person (name) VALUES ('George');
>>COMMIT;
>
>
> Just a very naive thought....
> Wouldn't make more sense to allow nested begin/commit/rollback blocks?
Is not naive because in this way you can do what you could do with
nested begin/commit/rollback blocks, in this way you can do more.
Think for example in the example above to convert the "ROLLBACK TO s3"
in a "ROLLBACK TO s1", that is impossible to do with begin/commit/rollback blocks.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: A transaction in transaction? Possible?
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
@ 2004-11-09 16:20 ` Bricklen <bricklen@zyahoo.zcomz>
2 siblings, 0 replies; 18+ messages in thread
From: Bricklen @ 2004-11-09 16:20 UTC (permalink / raw)
To: pgsql-sql
Andrei Bintintan wrote:
> //Is it possible to have another transatction in a transaction??? In the
> following example the last ROLLBACK is totally ignored(transaction1).
>
> ///connect to database/
> $database = dbConnect($dbhost, $dbuser, $dbpass, $dbname);
> dbExec($database, "BEGIN"); //transaction1
>
> ///*/
> / / dbExec($database, "BEGIN");//transaction2
> $sql = "UPDATE orders SET technikernotiz='51' WHERE id=16143";
> dbExec($database, $sql);
> dbExec($database, "COMMIT");//transaction2
> //**/
> /
> $sql = "UPDATE orders SET reklamationsdetail='51' WHERE id=16143";
> dbExec($database, $sql);
> dbExec($database, "ROLLBACK");//transaction1
>
> dbClose($database);
>
>
This appears to be the same as Oracle's "autonomous transactions", fwiw.
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2004-11-11 19:31 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-09 08:47 A transaction in transaction? Possible? Andrei Bintintan <klodoma@ar-sd.net>
2004-11-09 13:55 ` Michael Fuhr <mike@fuhr.org>
2004-11-09 15:24 ` Theodore Petrosky <tedpet5@yahoo.com>
2004-11-10 06:23 ` sad <sad@bankir.ru>
2004-11-10 06:25 ` Michael Fuhr <mike@fuhr.org>
2004-11-10 08:45 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-10 09:53 ` Michael Fuhr <mike@fuhr.org>
2004-11-10 10:58 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 10:37 ` Andrei Bintintan <klodoma@ar-sd.net>
2004-11-10 15:23 ` Peter Eisentraut <peter_e@gmx.net>
2004-11-10 16:01 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-10 16:09 ` Peter Eisentraut <peter_e@gmx.net>
2004-11-10 17:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-10 21:36 ` Stefan Weiss <spaceman@foo.at>
2004-11-11 08:23 ` Gaetano Mendola <mendola@bigfoot.com>
2004-11-11 19:31 ` Stefan Weiss <spaceman@foo.at>
2004-11-11 08:10 ` Gaetano Mendola <mendola@bigfoot.com>
2004-11-09 16:20 ` Bricklen <bricklen@zyahoo.zcomz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox