BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger - Mailing list pgsql-bugs

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:

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 = n + 10
WHERE id = 1;
-- do not commit, hold the row lock
------
Leave session 1 open

psql session 2:
-----
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t
SET n = n + 1
WHERE id = 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.