Re: transaction problem using cursors

> 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