A transaction in transaction? Possible?

This is a multi-part message in MIME format.

------=_NextPart_000_0140_01C4C649.74D89600
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Is it possible to have another transatction in a transaction??? In the =
following example the last ROLLBACK is totally ignored(transaction1).=20

//connect to database
$database =3D dbConnect($dbhost, $dbuser, $dbpass, $dbname);
dbExec($database, "BEGIN"); //transaction1

//*
dbExec($database, "BEGIN");//transaction2
$sql =3D "UPDATE orders SET technikernotiz=3D'51' WHERE =
id=3D16143";
dbExec($database, $sql);
dbExec($database, "COMMIT");//transaction2
/**/

$sql =3D "UPDATE orders SET reklamationsdetail=3D'51' WHERE =
id=3D16143";
dbExec($database, $sql);
dbExec($database, "ROLLBACK");//transaction1

dbClose($database);



------=_NextPart_000_0140_01C4C649.74D89600
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: A transaction in transaction? Possible?

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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: A transaction in transaction? Possible?

I thought nested transactions are available in the new
release (8) coming up.

Ted

--- 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);
>
>
>



__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: A transaction in transaction? Possible?

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.

Re: A transaction in transaction? Possible?

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 ?



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: A transaction in transaction? Possible?

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

Re: A transaction in transaction? Possible?

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
>


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: A transaction in transaction? Possible?

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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: A transaction in transaction? Possible?

Okay, I see you're speaking about pgsql 8.0

What about 7.4?

Andy.

----- Original Message -----
From: "Achilleus Mantzios"
To: "Michael Fuhr"
Cc: "Riccardo G. Facchini" ;
; "Theodore Petrosky" ; "Andrei
Bintintan" ; "sad"
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
>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: 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

Re: A transaction in transaction? Possible?

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/


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: A transaction in transaction? Possible?

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: A transaction in transaction? Possible?

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/


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: A transaction in transaction? Possible?

Achilleus Mantzios 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: A transaction in transaction? Possible?

On Wednesday, 10 November 2004 18:28, Tom Lane wrote:
> Achilleus Mantzios 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: A transaction in transaction? Possible?

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

Re: A transaction in transaction? Possible?

Stefan Weiss wrote:
> On Wednesday, 10 November 2004 18:28, Tom Lane wrote:
>
>>Achilleus Mantzios 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-con trol-structures.html#PLPGSQL-ERROR-TRAPPING

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

Re: A transaction in transaction? Possible?

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-con trol-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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend