From: Gaetano Mendola <mendola@bigfoot.com>
To: Stefan Weiss <spaceman@foo.at>
Subject: Re: A transaction in transaction? Possible?
Date: Thu, 11 Nov 2004 09:23:24 +0100
Message-ID: <4193217C.9080900@bigfoot.com> (raw)
In-Reply-To: <200411102236.41971.spaceman@foo.at>
References: <Pine.LNX.4.44.0411101256590.6883-100000@matrix.gatewaynet.com>
<13824.1100107697@sss.pgh.pa.us>
<200411102236.41971.spaceman@foo.at>
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-TRAPP...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mendola@bigfoot.com, spaceman@foo.at
Subject: Re: A transaction in transaction? Possible?
In-Reply-To: <4193217C.9080900@bigfoot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox