From: Stefan Weiss <spaceman@foo.at>
To: pgsql-sql@postgresql.org
Subject: Re: A transaction in transaction? Possible?
Date: Wed, 10 Nov 2004 22:36:41 +0100
Message-ID: <200411102236.41971.spaceman@foo.at> (raw)
In-Reply-To: <13824.1100107697@sss.pgh.pa.us>
References: <Pine.LNX.4.44.0411101256590.6883-100000@matrix.gatewaynet.com>
<13824.1100107697@sss.pgh.pa.us>
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.
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.
cheers,
stefan
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: spaceman@foo.at
Subject: Re: A transaction in transaction? Possible?
In-Reply-To: <200411102236.41971.spaceman@foo.at>
* 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