Question: In PostgreSQL it seems that if I perform a series of operations
in a transaction, everything goes peachy until I try to INSERT a row
that results in a duplicate key error.
The application then recovers from this particular error (anticipating the
possibility for it), and then continues the transaction. Unfortunately, the
PostgreSQL server (at least with libpq) reports that it is now in an
"abort state".. forcing ultimately the application to ROLLBACK and
start all over again. Ugh -- very painful.
Is this intended behavior?
In INFORMIX for example, the server keeps trucking until you
choose COMMIT or ROLLBACK. These operations of course only
work on the successful operations so far, but failed operations like
INSERTs are tolerated (ie. ignored).
The current PG behaviour forces the application to test for duplicate
insert problems first by performing a SELECT operation. This is
extra and unnecessary overhead, if the failed INSERTs did not
toss away the pending transaction.
I have used a specific example, of a larger issue. That being why
does the server have to enter "abort state", when failed portions
of the transaction can be ignored -- leaving the final decision
up to the application at the COMMIT/ROLLBACK point? I know
for a fact that INFORMIX works this way, and it is very
successfully exploited by applications.
Maybe this is just a libpq issue?
Using PostgreSQL 7.2.1 under FreeBSD 4.4/i386, on Pentium-III.
Thanks, Warren.
--
Warren W. Gay VE3WWG
http://home.cogeco.ca/~ve3wwg