BUG #17823: Generated columns not always updated correctly - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17823
Logged by:          Hisahiro Kauchi
Email address:      hisahiro@freemind.co.jp
PostgreSQL version: 15.2
Operating system:   CentOS 7
Description:

I found that the generated columns are sometimes not updated.

1. Create a table with a generated column, and insert a row:

    test=# create table test(id serial primary key, a int, b int generated
always as (a + 1) stored);
    CREATE TABLE
    test=# insert into test(a) values (1);
    INSERT 0 1
    test=# select * from test;
     id | a | b
    ----+---+---
      1 | 1 | 2
    (1 row)

2. Start Transaction A and update the row:

    ==== Transaction A ====

    test=# begin;
    BEGIN
    test=*# update test set a=2 where id=1;
    UPDATE 1
    test=*# select * from test;
     id | a | b
    ----+---+---
      1 | 2 | 3
    (1 row)

3. Before committing transaction A, start Transaction B to update the same
row:

    ==== Transaction B ====

    test=# begin;
    BEGIN
    test=*# update test set a=3 where id=1;

    (Waiting for Transaction A to commit)

4. Commit Transaction A:

    ==== Transaction A ====

    test=*# end;
    COMMIT

5. The UPDATE of Transaction B is executed:

    ==== Transaction B ====

    UPDATE 1
    test=*# end;
    COMMIT

6. Check the result:

    test=*# select * from test;
     id | a | b
    ----+---+---
      1 | 3 | 3
    (1 row)

The generated column "b" should be updated correctly to reflect the new
value of "a" (i.e., b=4).