From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Istvan Soos <istvan(dot)soos(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to cleanup transaction after statement_timeout aborts a query? |
Date: | 2024-09-08 11:18:28 |
Message-ID: | 8fabad5643b3b10355c95e30f4e758c17640d0d0.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Istvan Soos | 2024-09-08 13:01:51 | Re: How to cleanup transaction after statement_timeout aborts a query? |
Previous Message | Istvan Soos | 2024-09-08 10:56:33 | How to cleanup transaction after statement_timeout aborts a query? |