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 counter
SET value = value + 1
RETURNING value;
COMMIT;
and
UPDATE counter
SET 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 de documentation (Page 19), you can read:
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block
This means that all transactions including UPDATE
will be treated as transactions without having to explicitly specify them.