From: Gaetano Mendola <mendola@bigfoot.com>
Subject: Re: A transaction in transaction? Possible?
Date: Thu, 11 Nov 2004 09:23:24 +0100
Organization: PYRENET Midi-pyrenees Provider
Lines: 100
Message-ID: <4193217C.9080900@bigfoot.com>
References: <Pine.LNX.4.44.0411101256590.6883-100000@matrix.gatewaynet.com>
	<13824.1100107697@sss.pgh.pa.us>
	<200411102236.41971.spaceman@foo.at>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
To: Stefan Weiss <spaceman@foo.at>
User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103)
In-Reply-To: <200411102236.41971.spaceman@foo.at>
To: pgsql-sql@postgresql.org

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