Thread: how to continue a transaction after an error?
Hello, I would like to insert a bunch of rows in a table in a transaction. Some of the insertions will fail due to constraints violation. When this happens, Postgres automatically ends the transaction and rolls back all the previous inserts. I would like to continue the transaction and issue the commit/rollback command myself. How to do it? Is there any setting I am missing? Is it possible at all? Thanks, Cristi
On Mon, 13 Nov 2000, Cristi Petrescu-Prahova wrote: > Hello, > > I would like to insert a bunch of rows in a table in a transaction. Some of > the insertions will fail due to constraints violation. When this happens, > Postgres automatically ends the transaction and rolls back all the previous > inserts. I would like to continue the transaction and issue the > commit/rollback command myself. > > How to do it? > Is there any setting I am missing? > Is it possible at all? Currently, postgres treats all errors as critical ones that require a complete rollback of transaction (although I believe it does not immediately do the rollback, it should go into abort state). There's been on and off talk about changing this, but nothing really has been decided i believe.
On Mon, Nov 13, 2000 at 09:41:04PM +0200, Cristi Petrescu-Prahova wrote: > Hello, > > I would like to insert a bunch of rows in a table in a transaction. Some of > the insertions will fail due to constraints violation. When this happens, > Postgres automatically ends the transaction and rolls back all the previous > inserts. I would like to continue the transaction and issue the > commit/rollback command myself. > > How to do it? > Is there any setting I am missing? > Is it possible at all? Patient: "Doctor, it hurts when I bend my arm behind my back like this. Can you help me?" Doctor: "Sure, don't do that." But seriously, this comes up from time to time. PostgreSQL is a little stricter than most DBMSs with regards to transactional semantics, but there are good reasons for this, involving tradeoffs of locking, MVCC, "autocommit" mode, etc. Let's look at transactions in general. When you start a transaction, you're telling the backend "treat all of these statements as one, big, all or nothing event." Just the thing for, say, balance transfers in a bookkeeping application, but not something you need for storing web log URL hits. If the backend isn't strict, how would you want it to distinguish between "I really mean it, this time" and "that's o.k., go ahead anyway?" If you want (need, if you're using large objects) transactions, you really need to think about your transaction boundries. Don't just wrap your whole frontend in one big, long lived transaction: close and reopen your transaction for those inserts that are allowed to fail. Or don't do them inside a multistatement transaction at all: let each one run in it's own transaction space (i.e. other databases "autocommit" mode) Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
I believe the known mechanism to cope with errors inside transactions are savepoints. That doesn't seem to be the case, though. If you don't care if some inserts fail, then you have multiple transactions instead of just one (use autocommit). If you want a transaction, check existence before each insert. That's the way to do it. > -----Original Message----- > I would like to insert a bunch of rows in a table in a > transaction. Some of > the insertions will fail due to constraints violation. When this happens, > Postgres automatically ends the transaction and rolls back all > the previous > inserts. I would like to continue the transaction and issue the > commit/rollback command myself.
Multiple transactions carry a price: one cannot isolate the insertions from other sessions. This is not desirable in my case. The second suggestion is valuable. Thanks, Cristi ----- Original Message ----- From: Edmar Wiggers <edmar@brasmap.com> To: Cristi Petrescu-Prahova <cristipp@lasting.ro>; <pgsql-sql@postgresql.org> Sent: Monday, November 13, 2000 10:13 PM Subject: RE: [SQL] how to continue a transaction after an error? > > I believe the known mechanism to cope with errors inside transactions are > savepoints. That doesn't seem to be the case, though. > > If you don't care if some inserts fail, then you have multiple transactions > instead of just one (use autocommit). > > If you want a transaction, check existence before each insert. That's the > way to do it. > > > -----Original Message----- > > I would like to insert a bunch of rows in a table in a > > transaction. Some of > > the insertions will fail due to constraints violation. When this happens, > > Postgres automatically ends the transaction and rolls back all > > the previous > > inserts. I would like to continue the transaction and issue the > > commit/rollback command myself.
At 14:08 13/11/00 -0600, Ross J. Reedstrom wrote: >On Mon, Nov 13, 2000 at 09:41:04PM +0200, Cristi Petrescu-Prahova wrote: >> Hello, >> >> I would like to insert a bunch of rows in a table in a transaction. Some of >> the insertions will fail due to constraints violation. When this happens, >> Postgres automatically ends the transaction and rolls back all the previous >> inserts. I would like to continue the transaction and issue the >> commit/rollback command myself. >> >> How to do it? >> Is there any setting I am missing? >> Is it possible at all? > Not possible; the error handling in PGSQL is a bit of a mess (not necessarily a fault of PG), and it's not possible (currently) to rollback single statements inside a larger transaction. This feature has to come, but *not* AFAICT in the next release. >But seriously, this comes up from time to time. PostgreSQL is a little >stricter than most DBMSs with regards to transactional semantics, but >there are good reasons for this, involving tradeoffs of locking, MVCC, >"autocommit" mode, etc. Not to mention the fact that we did not support sub-transactions. But I think WAL does this for us, so we can hope for the feature RSN. >When you start a transaction, >you're telling the backend "treat all of these statements as one, big, >all or nothing event." This is actually contrary to the standard. Statements are atomic, and a failed statement should not abort the TX: The execution of all SQL-statements other than SQL-control statements is atomic with respect to recovery. Such an SQL-statement is called an atomic SQL-statement. ... An SQL-transaction cannot be explicitly terminated within an atomic execution context. If the execution of an atomic SQL-statement is unsuccessful, then the changes to SQL-data or schemas made by the SQL-statement are canceled. >If you want (need, if you're using large objects) transactions, you >really need to think about your transaction boundries. Don't just wrap >your whole frontend in one big, long lived transaction Totally agree; transactions will keep locks. Release them as soon as the business rules and application design says that you can. Note that commit-time constraints may make the commit fail; in this case PG will force a rollback, but it *should* allow corrective action and another attempt at a commit. >close and reopen >your transaction for those inserts that are allowed to fail. This is very good advice for PGSQL, but bad advice for general DB programming. At the end of the day, the database is the final arbiter of valid data (through triggers, constraints etc that implement business rules). Since we don't want to duplicate all of the rules from the database within the application, we need to rely on the database telling us that the last operation failed so that we can *choose* to rollback or choose to change the processing. A classic example would be processing batches of data - open a file, read a line, insert it, if the data is bad, insert it into a list of exceptions, keep reading file; this needs to be done in one TX since after commit we delete the file, and we don't want partially loaded batches if the machine crashes. I admit this example demonstrates my age, but this kind of processing is still necessary. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
> >When you start a transaction, > >you're telling the backend "treat all of these statements as one, big, > >all or nothing event." > > This is actually contrary to the standard. Statements are atomic, and a > failed statement should not abort the TX: > > The execution of all SQL-statements other than SQL-control > statements is atomic with respect to recovery. Such an > SQL-statement is called an atomic SQL-statement. > > ... > > An SQL-transaction cannot be explicitly terminated within an > atomic execution context. If the execution of an atomic > SQL-statement is unsuccessful, then the changes to SQL-data or schemas > made by the SQL-statement are canceled. This I agree with in general. You can almost defend the current behavior by saying all errors cause an "unrecoverable error" (since I don't see a definition of unreverable errors), but we're doing that wrong too since that should initiate a rollback as opposed to our current behavior. Admittedly, having an SQLSTATE style error code would help once we had that so you could actually figure out what the error was. > >If you want (need, if you're using large objects) transactions, you > >really need to think about your transaction boundries. Don't just wrap > >your whole frontend in one big, long lived transaction > > Totally agree; transactions will keep locks. Release them as soon as the > business rules and application design says that you can. Note that > commit-time constraints may make the commit fail; in this case PG will > force a rollback, but it *should* allow corrective action and another > attempt at a commit. This I disagree with for commit time constraints unless stuff was changed between the draft I have and final wording:"When a <commit statement> is executed, all constraints are effectivelychecked and, if any constraint is not satisfied, then an exception condition is raised and the transactionis terminated by an implicit <rollback statement>." Other places they are a little less explicit about failed commits, but it certainly allows a cancelation of changes:"If an SQL-transaction is terminated by a <rollback statement> or unsuccessfulexecution of a <commit statement>, then all changes made to SQL-data or schemas by that SQL-transactionare canceled. Committed changes cannot be canceled. If execution of a <commit statement> is attempted,but certain exception conditions are raised, it is unknown whether or not the changes made to SQL-dataor schemas by that SQL-transaction are canceled or made persistent. And I think this makes sense. If you're committing then you're saying you're done and that you want the transaction to go away. If you just want to check deferred constraints, there's set constraints mode. I could almost see certain recoverable internal state things being worth not doing a rollback for, but not constraints.
At 22:23 13/11/00 -0800, Stephan Szabo wrote: >Admittedly, having an SQLSTATE style error code would help once we had >that so you could actually figure out what the error was. Yep, that would be nice. >> Totally agree; transactions will keep locks. Release them as soon as the >> business rules and application design says that you can. Note that >> commit-time constraints may make the commit fail; in this case PG will >> force a rollback, but it *should* allow corrective action and another >> attempt at a commit. > >This I disagree with for commit time constraints unless stuff was changed >between the draft I have and final wording: > "When a <commit statement> is executed, > all constraints are effectively checked and, if any constraint > is not satisfied, then an exception condition is raised and the > transaction is terminated by an implicit <rollback statement>." Just checked the SQL99 stuff, and you are quite right - commit it terminal no matter what. >If you're committing then you're saying >you're done and that you want the transaction to go away. Not only that, but trying to unravel a constraint failure at commit-time would (except in trivial cases) be almost impossible. Best thing is to rollback. >If you just >want to check deferred constraints, there's set constraints mode. True. >I could >almost see certain recoverable internal state things being worth not doing >a rollback for, but not constraints. Not true, eg, for FK constraints. The solution may be simple and the application needs the option to fix it. Also, eg, the triggered data *could* be useful in reporting the error (or fixing it in code), so an implied rollback is less than ideal. Finally, custom 'CHECK' constraints could be designed for exactly this purpose (I have done this in DBs before). ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
On Tue, 14 Nov 2000, Philip Warner wrote: > >I could > >almost see certain recoverable internal state things being worth not doing > >a rollback for, but not constraints. > > Not true, eg, for FK constraints. The solution may be simple and the > application needs the option to fix it. Also, eg, the triggered data > *could* be useful in reporting the error (or fixing it in code), so an > implied rollback is less than ideal. Finally, custom 'CHECK' constraints > could be designed for exactly this purpose (I have done this in DBs before). I was actually talking about commit time rollback there, not statement time. I could theoretically see commit time non-rollback in cases of a presumed transient internal state thing (now, I can't think of any in practice, but...) For a commit time check, I still think preceding with a set constraints all immediate is better if you want to actually see if you're safe to commit.
> I would like to insert a bunch of rows in a table in a > transaction. Some of > the insertions will fail due to constraints violation. When > this happens, > Postgres automatically ends the transaction and rolls back > all the previous > inserts. I would like to continue the transaction and issue the > commit/rollback command myself. > > How to do it? > Is there any setting I am missing? > Is it possible at all? Hopefully, we'll have savepoints in 7.2 Vadim