Re: how to continue a transaction after an error?

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 |/