Thread: transaction problem using cursors
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
Hello
I thing so problem is there
AND Cast("CUSTOMERS"."ZIP" as integer) < 20000
You cannot cast 'A1234' to integer
Regards
Pavel Stehule
2007/6/11, Pit M. <fmi-soft@gmx.de>:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Hi, On Monday 11 June 2007 13:45, Pit M. wrote: | 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. I didn't try myself, but wrapping the whole into a PL/pgSQL function and using exceptions might do the work; http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Ciao, Thomas -- Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
Pavel Stehule schrieb:
> Hello
>
> I thing so problem is there
>
> AND Cast("CUSTOMERS"."ZIP" as integer) < 20000
>
> You cannot cast 'A1234' to integer
>
> Regards
> Pavel Stehule
>
Yes i know that i can't, but why the transaction fails?
Pit
> > I didn't try myself, but wrapping the whole into a PL/pgSQL function and > using exceptions might do the work; > > It's not good advice. I tested it, and problem is in where clause. I don't understand problem well, but one possibility is change from cast to to_number function like: postgres=# begin; BEGIN postgres=# declare c cursor for select * from fx where to_number(b,'99999') > 0; DECLARE CURSOR postgres=# fetch from c; a | b ----+---- 10 | 20 (1 row) postgres=# fetch from c; a | b ----+----- 10 | a20 (1 row) But still this solution is +/- correct Regards Pavel
> > I didn't try myself, but wrapping the whole into a PL/pgSQL function and > using exceptions might do the work; > > http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > Ciao, > Thomas > Unfortunately this is not possible, because this should happen on the client. The client calls FETCH for every record available in that cursor when the user (application) wants to display the data (scrollable list of records) So do you think i should wrap each FETCH statement? 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. Pit
> > > Yes i know that i can't, but why the transaction fails? > because casting fails. First FETCH was ok, and evaluating of next row (which need second FETCH) was broken. When any statement in transaction fail, you have to rollback current transaction. Pavel
> > Unfortunately this is not possible, because this should happen on the > client. The client calls FETCH for every record available in that cursor > when the user (application) wants to display the data (scrollable > list of records) > So do you think i should wrap each FETCH statement? > 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. > Change your SQL statement. It is not correct with your real data. Pavel
Thank you Pavel! The problem is, that the CAST function may be used in any SQL query, since our software allows using a free SQL-query. The query itself doesn't fail, because the syntax is correct. The problem is that there may be other functions in a query that can lead to a runtime error during a FETCH. The goal is to maintain a valid cursor that can be used to FETCH other records. It would be ideal to skip this record an continue fetching the next record -> but how can i achieve this? Thanks Pit > It's not good advice. I tested it, and problem is in where clause. I > don't understand problem well, but one possibility is change from cast > to to_number function like: > > postgres=# begin; > BEGIN > postgres=# declare c cursor for select * from fx where > to_number(b,'99999') > 0; > DECLARE CURSOR > postgres=# fetch from c; > a | b > ----+---- > 10 | 20 > (1 row) > > postgres=# fetch from c; > a | b > ----+----- > 10 | a20 > (1 row) > > But still this solution is +/- correct > > Regards > Pavel > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
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...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment
> 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
"Pit M." <fmi-soft@gmx.de> writes: > The goal is to maintain a valid cursor that can be used to FETCH other records. > It would be ideal to skip this record an continue fetching the next record -> > but how can i achieve this? Well ideally the answer would be to wrap each FETCH in a savepoint and roll back to the savepoint if it fails. However I just tried it and this doesn't work. So I think the answer is you can't, sorry. One alternative would be to store the result set in a temporary table with an index on it. Then you could execute a separate query for each record you want to look up and wrap that query in a savepoint if you want to do it in a transaction and recover from errors. It wouldn't have the same performance profile as using a cursor. It would use more resources in most cases and the lookups will be slower. Though if you keep this data around for a long time you might actually be better off since you could use a real non-temporary table and not be forced to keep around transactions for long periods of time tying up other resources. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> writes:
> "Pit M." <fmi-soft@gmx.de> writes:
>> The goal is to maintain a valid cursor that can be used to FETCH other records.
>> It would be ideal to skip this record an continue fetching the next record ->
>> but how can i achieve this?
> Well ideally the answer would be to wrap each FETCH in a savepoint and roll
> back to the savepoint if it fails. However I just tried it and this doesn't
> work. So I think the answer is you can't, sorry.
The problem is to distinguish failures that don't imply the cursor
itself is broken from failures that do. What I'd suggest is reducing
the cursor definition to a simple "SELECT * FROM ..." and then doing
the failure-prone calculations separately. The cursor won't get marked
PORTAL_FAILED unless the error occurs during a FETCH from it.
regards, tom lane