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

On Sun, 2024-09-08 at 15:01 +0200, Istvan Soos wrote:
> On Sun, Sep 8, 2024 at 1:19 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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