On Wednesday, 10 November 2004 18:28, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match