Re: A transaction in transaction? Possible?

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.

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