On Mon, Jun 11, 2007 at 03:28:22PM +0200, Pit M. wrote:
We handle exceptions errors through libpq, and if a FETCH leads to such
a runtime error, we try to FETCH the first record again.
The problem is that we can't use this cursor any more -> it seems to be
corrupt after that error.
An aborted transaction is an aborted transaction, you have to rollback
to a known state before you can go on.
I'm not sure why you can't just change the queries, but in the
particluar case you give, why not just treat them as strings to start
with:
WHERE "CUSTOMERS"."ZIP" >= '10000'
AND "CUSTOMERS"."ZIP" < '20000'
That will produce the same result, but without any chance of errors...
Thank you Martijn!
I know that CAST is not ideal for this query :-) , but my customers are
free to define their own queries. PG hat thousands of functions to use
in queries. Which one of them will lead to similar problems??
>>An aborted transaction is an aborted transaction,
Why was it completely aborted?
Transactions are a problem using cursors, i think PG doesn't support
this properly. We had to use savepoints to handle syntax errors in the
cursor declaration
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
