Subject: Do postgres use implicit transaction in UPDATE ... RETURNING queries?

From: Debraj Manna <[email-masked]>
Date: Fri, 29 Nov 2024 15:35:30 +0530

Can someone let me know if Postgres 15 uses implicit transactions for
UPDATE...RETURNING queries?

For example, is there any differences between the two queries

BEGIN;UPDATE counterSET value = value + 1
RETURNING value;COMMIT;

and

UPDATE counterSET value = value + 1
RETURNING value;

Table is like below

CREATE TABLE IF NOT EXISTS counter
(
    value      bigint PRIMARY KEY NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ        NOT NULL DEFAULT CURRENT_TIMESTAMP
);

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 29 Nov 2024 11:10:16 +0100

On Fri, 2024-11-29 at 15:35 +0530, Debraj Manna wrote:
> Can someone let me know if Postgres 15 uses implicit transactions for UPDATE...RETURNING queries?

Yes, PostgreSQL always uses autocommit.

Yours,
Laurenz Albe

---

From: Peter Gram <[email-masked]>
Date: Fri, 29 Nov 2024 13:39:12 +0100

Hi Laurenz et al

I would like to say in this way : Many clients like psql set autocommit on
as default.
It is up to the client to define the default and there are clients that
default to autocommit off.

Med venlig hilsen

Peter Gram
Sæbyholmsvej 18
2500 Valby

Mobile: (+45) 5374 7107
Email: [email-masked]

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 29 Nov 2024 14:09:31 +0100

On Fri, 2024-11-29 at 13:39 +0100, Peter Gram wrote:
> I would like to say in this way : Many clients like psql set autocommit on as default. 
> It is up to the client to define the default and there are clients that default to autocommit off.

The server is always in autocommit mode.

Clients can fake "autocommit off" by tacitly sending transaction control statements.

Yours,
Laurenz Albe