If in a transaction block, one of the queries generates an ERROR
then the transaction is aborted prior to a COMMIT/ROLLBACK being ordered,
but still requires a COMMIT/ROLLBACK to officially end the transaction.
I was expecting to have to programmaticly check for the error(s) and either
attempt to fix the issue or explicitly ROLLBACK. While the behavoir
is extremely convienent for simple transactions, it seems to create a great
deal of extra work in cases where you want to try to recover from error rather
that aborting. To do so now, I would have to
1) issue query
2) check for error
No Error) save the query to some log
Error) issue the explict rollback to end the transaction
program logic to recover
start new transaction
issue all saved query from the log
issue recovered version of query
This is a fair bit of work. However, I do realize that its rarely needed.
If its possible to programmatically recover from an error, its
should be possible to detect the error in advance 99.9% of the time and
avoid generating the error in the first place. However, in cases where errors
are very rarely expected, and testing for errors in advance is a performance
issue, the above pattern would seem to be appropriate. Is there a way to
disable the implict abort when it makes sense?
This behavoir is alluded to in the docs, but not stated explicity; it is
raised directly in one of the user-provided notes on BEGIN and is noted
to be at odds with other DBMS's. Can this behavoir be counted on for the
forseeable future versions of PostGreSQL?
--- begin demonstration script ---
CREATE TABLE foo (
bar int primary key,
baz int
);
BEGIN WORK;
INSERT INTO foo (bar,baz) VALUES (1,2);
SELECT joe FROM foo;
COMMIT WORK;
SELECT * FROM foo;
--- end demonstration script ---
Flipping the INSERT/SELECT in the transaction block raises the NOTICE
referenced in the subject line and clued me in to what was happening.
Thank you.
Eric Nielsen