Re: How to cleanup transaction after statement_timeout aborts a query?

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