Message-ID: <8fabad5643b3b10355c95e30f4e758c17640d0d0.camel@cybertec.at>
Subject: Re: How to cleanup transaction after statement_timeout aborts a
 query?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Istvan Soos <istvan.soos@gmail.com>, pgsql-general@lists.postgresql.org
Date: Sun, 08 Sep 2024 13:18:28 +0200
In-Reply-To: <CALdQGgv_an=JdfkomND7sJcuAS1L5OmbFzhQa9qcW48syTXyKg@mail.gmail.com>
References: 
	<CALdQGgv_an=JdfkomND7sJcuAS1L5OmbFzhQa9qcW48syTXyKg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/8fabad5643b3b10355c95e30f4e758c17640d0d0.camel%40cybertec.at>
Precedence: bulk

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:
>=20
> setup:
> CREATE TABLE t (id INT PRIMARY KEY);
> INSERT INTO t (id) values (1);
>=20
> client-1:
> BEGIN;
> SELECT * FROM t WHERE id=3D1 FOR UPDATE;
> <client sleeps for a while>
>=20
> client-2:
> BEGIN;
> SET statement_timeout TO 1000;
> SELECT * FROM t WHERE id=3D1 FOR UPDATE;
> <server sends error message with the timeout>
>=20
> After that any query I send through client-2 will get me the following er=
ror:
>=20
> Severity.error 25P02: current transaction is aborted, commands ignored
> until end of transaction block
>=20
> 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 statemen=
ts
succeed, or all fail.

I am aware that other databases have a "statement rollback" feature that al=
lows
the transaction to proceed after an error, but PostgreSQL doesn't.

To handle the failure of a statement while allowing the transaction to proc=
eed,
you can use savepoints.  But be warned: don't even think of setting a savep=
oint
before each statement.  That would affect statement performance severely.

Yours,
Laurenz Albe