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

From: Istvan Soos <[email-masked]>
Date: Sun, 8 Sep 2024 15:01:51 +0200

On Sun, Sep 8, 2024 at 1:19 PM Laurenz Albe <[email-masked]> 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

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 08 Sep 2024 15:41:03 +0200

> 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=> 

> 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