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/rollbackblocks?>>>>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
problemswith 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
"deletecontact" 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
calledas 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