Re: A transaction in transaction? Possible?

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