Thread: How to cleanup transaction after statement_timeout aborts a query?
I'm one of the developers of the Dart-language Postgresql client package. I am working on adding a feature that may set the statement_timeout value before a session or a query as the client requests it, however, I'm stuck with the following error: setup: CREATE TABLE t (id INT PRIMARY KEY); INSERT INTO t (id) values (1); client-1: BEGIN; SELECT * FROM t WHERE id=1 FOR UPDATE; <client sleeps for a while> client-2: BEGIN; SET statement_timeout TO 1000; SELECT * FROM t WHERE id=1 FOR UPDATE; <server sends error message with the timeout> After that any query I send through client-2 will get me the following error: Severity.error 25P02: current transaction is aborted, commands ignored until end of transaction block Not even ROLLBACK or COMMIT is working. It is the same for both simple and extended query protocol. Does the client need to send a non-query message to cleanup the transaction state? Or is this connection now gone for good? Thanks, Istvan
On Sun, 2024-09-08 at 12:56 +0200, Istvan Soos wrote: > I'm one of the developers of the Dart-language Postgresql client > package. I am working on adding a feature that may set the > statement_timeout value before a session or a query as the client > requests it, however, I'm stuck with the following error: > > setup: > CREATE TABLE t (id INT PRIMARY KEY); > INSERT INTO t (id) values (1); > > client-1: > BEGIN; > SELECT * FROM t WHERE id=1 FOR UPDATE; > <client sleeps for a while> > > client-2: > BEGIN; > SET statement_timeout TO 1000; > SELECT * FROM t WHERE id=1 FOR UPDATE; > <server sends error message with the timeout> > > After that any query I send through client-2 will get me the following error: > > Severity.error 25P02: current transaction is aborted, commands ignored > until end of transaction block > > Not even ROLLBACK or COMMIT is working. It is the same for both simple > and extended query protocol. Does the client need to send a non-query > message to clean up the transaction state? Or is this connection now > gone for good? ROLLBACK and COMMIT are working: they end the transaction. It is the atomicity guarantee of database transactions: either all statements succeed, or all fail. I am aware that other databases have a "statement rollback" feature that allows the transaction to proceed after an error, but PostgreSQL doesn't. To handle the failure of a statement while allowing the transaction to proceed, you can use savepoints. But be warned: don't even think of setting a savepoint before each statement. That would affect statement performance severely. Yours, Laurenz Albe
On Sun, Sep 8, 2024 at 1:19 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > ROLLBACK and COMMIT are working: they end the transaction. I have this reproduction test, and ROLLBACK does fail: https://github.com/isoos/postgresql-dart/pull/363/files#diff-4547e49b04ec8280fb8f4f1ebf695b77f9a2d9a4ac9bcfd685bcd570a46baa80R122 I've checked and nothing else is sent on the protocol, yet, for the rollback statement it gets the 25P02 error. > It is the atomicity guarantee of database transactions: either all statements > succeed, or all fail. Yeah, I thought so, that's why I'm struggling to see what's missing. > To handle the failure of a statement while allowing the transaction to proceed, > you can use savepoints. But be warned: don't even think of setting a savepoint > before each statement. That would affect statement performance severely. As the writer of the client library, I don't have the luxury of telling users they need to change their way, hence I'm looking for any pointer on the protocol level. Thanks, Istvan
On Sun, 2024-09-08 at 15:01 +0200, Istvan Soos wrote: > On Sun, Sep 8, 2024 at 1:19 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > ROLLBACK and COMMIT are working: they end the transaction. > > I have this reproduction test, and ROLLBACK does fail: > https://github.com/isoos/postgresql-dart/pull/363/files#diff-4547e49b04ec8280fb8f4f1ebf695b77f9a2d9a4ac9bcfd685bcd570a46baa80R122 > > I've checked and nothing else is sent on the protocol, yet, for the > rollback statement it gets the 25P02 error. You must be doing something wrong then, because it works: test=> START TRANSACTION; START TRANSACTION test=*> SELECT 1 / 0; ERROR: division by zero test=!> SELECT 42; ERROR: current transaction is aborted, commands ignored until end of transaction block test=!> SELECT 42; ERROR: current transaction is aborted, commands ignored until end of transaction block test=!> ROLLBACK; ROLLBACK test=> > > > To handle the failure of a statement while allowing the transaction to proceed, > > you can use savepoints. But be warned: don't even think of setting a savepoint > > before each statement. That would affect statement performance severely. > > As the writer of the client library, I don't have the luxury of > telling users they need to change their way, hence I'm looking for any > pointer on the protocol level. I understand. But there is no way to influence this behavior. Perhaps the best option for your library is not to try to "mask" the way PostgreSQL behaves in this case. It is then up to the users of the library to do the right thing. Yours, Laurenz Albe
On Sun, Sep 8, 2024 at 3:41 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > You must be doing something wrong then, because it works: And you are right, I was doing something wrong: The combination of the change and the library internals triggered a SET statement_timeout TO 3000; before the ROLLBACK was sent, and the error message I got was not clear on which statement triggered it. I'm sorry for the noise. Btw. Is there any guideline on how to write the client libraries or recommendations/tests that a client library should follow? Thanks, Istvan
On Sun, 2024-09-08 at 16:02 +0200, Istvan Soos wrote: > On Sun, Sep 8, 2024 at 3:41 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > You must be doing something wrong then, because it works: > > And you are right, I was doing something wrong: > > [...] > > I'm sorry for the noise. No problem; I'm glad you could find the stumbling stone. > Btw. Is there any guideline on how to write the client libraries or > recommendations/tests that a client library should follow? I'm afraid this question is too generic for an answer... I'd say it should be easy and intuitive to use, and should be as robust as possible (return meaningful error messages rather than crash). Yours, Laurenz Albe