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
