We use PG 8.2.4 with as cursors over libpq and get an error:
ERROR: current transaction is aborted, commands ignored until end of
transaction block
How can we avoid this error or make the cursor still working afterwards?
Assume following contents of table CUSTOMERS:
ID | ZIP (varchar(5))
------ | -----------------------
1 | 12345
2 | 12346
3 | 99999
4 | A1234
Assume a user doing a query which shall select all customers having a
ZIP in [10000;20000[ by using a CAST. If the cursor now fetches the row
with ID 4 we get an error (invalid input syntax) as "A1234" cannot be
casted as integer. This is ok, BUT now all further FETCH commands fail
with "current transaction is aborted"!
How can we resume from there with FETCH commands (e.g. at least FETCH
FIRST)? We cannot avoid the (first) error itself, as the user can enter
any WHERE condition at runtime. Therefore we must handle the error - if
any - and resume from there properly.
Refer to following log:
SAVEPOINT tsp_020DE240
DECLARE c020DE860 SCROLL CURSOR FOR
SELECT "CUSTOMERS"."ID","CUSTOMERS"."ZIP"
FROM "CUSTOMERS"
WHERE Cast("CUSTOMERS"."ZIP" as integer) >= 10000
AND Cast("CUSTOMERS"."ZIP" as integer) < 20000
FETCH FROM c020DE860
RELEASE SAVEPOINT tsp_020DE240
FETCH FIRST FROM c020DE860
FETCH FORWARD FROM c020DE860 -> OK
FETCH FORWARD FROM c020DE860 -> ERROR: invalid input syntax for
integer: "A1234"
FETCH FORWARD FROM c020DE860 -> ERROR: current transaction is aborted,
commands ignored until end of transaction block