Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent
 update when table has a BEFORE UPDATE trigger
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: bobergj@gmail.com
Reply-To: bobergj@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Wed, 24 Jun 2026 15:22:14 +0000
Message-ID: <19536-73ce5847e6c0e7b1@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19536-73ce5847e6c0e7b1%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19536
Logged by:          Jonas Boberg
Email address:      bobergj@gmail.com
PostgreSQL version: 18.4
Operating system:   Docker image postgres:18.4-alpine3.23
Description:       =20

In an UPDATE ... RETURNING OLD under isolation level READ COMMITTED, when
the table has a BEFORE UPDATE trigger, the OLD value is stale after a
concurrent update.

SELECT version();
> PostgreSQL 18.4 on aarch64-unknown-linux-musl, compiled by gcc (Alpine
15.2.0) 15.2.0, 64-bit

## How to reproduce

Setup:
-------
CREATE TABLE t (
  id int PRIMARY KEY,
  n  int NOT NULL
);

CREATE FUNCTION before_update_noop()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RETURN NEW;
END;
$$;

CREATE TRIGGER t_before_update
BEFORE UPDATE ON t
FOR EACH ROW EXECUTE FUNCTION before_update_noop();

INSERT INTO t VALUES (1, 7);
-----

psql session 1:
------
BEGIN ISOLATION LEVEL READ COMMITTED;

UPDATE t
SET n =3D n + 10
WHERE id =3D 1;
-- do not commit, hold the row lock
------
Leave session 1 open

psql session 2:
-----
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t
SET n =3D n + 1
WHERE id =3D 1
RETURNING
  OLD.n AS old_n,
  NEW.n AS new_n,
  NEW.n - 1 AS expected_old;
----
Session 2 blocks. Now commit in session 1:
----
COMMIT;
----

## Actual result

Session 2 outputs:
 old_n | new_n | expected_old
-------+-------+--------------
     7 |    18 |           17
(1 row)

Here OLD.n is the pre-concurrent update value, while NEW.n is the value
written by the concurrent update.

## Expected result

 old_n | new_n | expected_old
-------+-------+--------------
    17 |    18 |           17

If I drop the trigger and repeat the test, session 2 returns the expected
result.