public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Istvan Soos <[email protected]>
To: [email protected]
Subject: Re: How to cleanup transaction after statement_timeout aborts a query?
Date: Sun, 08 Sep 2024 13:18:28 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CALdQGgv_an=JdfkomND7sJcuAS1L5OmbFzhQa9qcW48syTXyKg@mail.gmail.com>
References: <CALdQGgv_an=JdfkomND7sJcuAS1L5OmbFzhQa9qcW48syTXyKg@mail.gmail.com>

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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: How to cleanup transaction after statement_timeout aborts a query?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox