Re: A transaction in transaction? Possible?

Stefan Weiss wrote:
> On Wednesday, 10 November 2004 18:28, Tom Lane wrote:
>
>>Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)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