Message-ID: <1643e5dbd663bbbc8f2458c8dab08da342529643.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>
Cc: pgsql-general@lists.postgresql.org
Date: Sun, 08 Sep 2024 15:41:03 +0200
In-Reply-To: <CALdQGgtteEQYNWhT80gNG3xSsjw+p1xDhFiG-+CUMXR9Gy=wBg@mail.gmail.com>
References: 
	<CALdQGgv_an=JdfkomND7sJcuAS1L5OmbFzhQa9qcW48syTXyKg@mail.gmail.com>
	 <8fabad5643b3b10355c95e30f4e758c17640d0d0.camel@cybertec.at>
	 <CALdQGgtteEQYNWhT80gNG3xSsjw+p1xDhFiG-+CUMXR9Gy=wBg@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/1643e5dbd663bbbc8f2458c8dab08da342529643.camel%40cybertec.at>
Precedence: bulk

On Sun, 2024-09-08 at 15:01 +0200, Istvan Soos wrote:
> On Sun, Sep 8, 2024 at 1:19=E2=80=AFPM Laurenz Albe <laurenz.albe@cyberte=
c.at> wrote:
> > ROLLBACK and COMMIT are working: they end the transaction.
>=20
> I have this reproduction test, and ROLLBACK does fail:
> https://github.com/isoos/postgresql-dart/pull/363/files#diff-4547e49b04ec=
8280fb8f4f1ebf695b77f9a2d9a4ac9bcfd685bcd570a46baa80R122
>=20
> 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=3D> START TRANSACTION;
START TRANSACTION
test=3D*> SELECT 1 / 0;
ERROR:  division by zero
test=3D!> SELECT 42;
ERROR:  current transaction is aborted, commands ignored until end of trans=
action block
test=3D!> SELECT 42;
ERROR:  current transaction is aborted, commands ignored until end of trans=
action block
test=3D!> ROLLBACK;
ROLLBACK
test=3D>=20

>=20
> > 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 s=
avepoint
> > before each statement.  That would affect statement performance severel=
y.
>=20
> 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 Po=
stgreSQL
behaves in this case.  It is then up to the users of the library to do the =
right thing.

Yours,
Laurenz Albe