Re: [SQL] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] A transaction in transaction? Possible?
Stefan Weiss wrote: On Wednesday, 10 November 2004 18:28, Tom Lane wrote: Achilleus Mantzios [EMAIL PROTECTED] 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-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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] 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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] 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-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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] 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: [SQL] 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: [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
Re: [SQL] 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 [EMAIL PROTECTED])
Re: [SQL] 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: [SQL] 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: [SQL] A transaction in transaction? Possible?
On Wednesday, 10 November 2004 18:28, Tom Lane wrote: Achilleus Mantzios [EMAIL PROTECTED] 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
[SQL] A transaction in transaction? Possible?
Is it possible to have another transatction in a transaction??? In the following example the lastROLLBACK 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");//transaction1dbClose($database);
Re: [SQL] 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 [EMAIL PROTECTED])
Re: [SQL] A transaction in transaction? Possible?
I thought nested transactions are available in the new release (8) coming up. Ted --- Andrei Bintintan [EMAIL PROTECTED] 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: [SQL] 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
