BUG #19081: Inconsistent target rows seen by the UPDATE and INSERT statements within the same transaction

The following bug has been logged on the website:

Bug reference:      19081
Logged by:          Ziyu Cui
Email address:      cuiziyu20@otcaix.iscas.ac.cn
PostgreSQL version: 18.0
Operating system:   Ubuntu-20.04
Description:

At the Repeatable Read isolation level, the target rows seen by UPDATE and
INSERT within the same transaction are inconsistent.

Steps to reproduce:
/* init */ CREATE TABLE t0 (c1 INT PRIMARY KEY, c2 INT);
/* init */ INSERT INTO t0 (c1, c2) VALUES (0, 0);

 -- TRANSACTION 1;
BEGIN;
UPDATE t0 SET c1 = 5, c2 = 5 WHERE c2 = 0;
COMMIT;

 -- TRANSACTION 2;
BEGIN;
INSERT INTO t0 (c1, c2) VALUES (2, 2);
INSERT INTO t0 (c1, c2) VALUES (0, 12);
UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 5;
COMMIT;

-- Submit Order
/* T1 */ BEGIN;
/* T1 */ UPDATE t0 SET c1 = 5, c2 = 5 WHERE c2 = 0;
/* T2 */ BEGIN;
/* T2 */ INSERT INTO t0 (c1, c2) VALUES (2, 2);
/* T1 */ COMMIT;
/* T2 */ INSERT INTO t0 (c1, c2) VALUES (0, 12);
/* T2 */ UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 5;
/* T2 */ COMMIT;

SELECT * FROM t0;

 c1 | c2
----+----
  5 |  5
  2 |  2
  0 | 12

After transaction 1 was committed, the second INSERT in transaction 2
successfully inserted the value (0, 12) because transaction 1 had updated
the row (0, 0). However, the UPDATE in transaction 2 did not update the row
(5, 5) that were updated in transaction 1.
The final database result seems to indicate that the second INSERT in
transaction 2 can see the update made by transaction 1, but the UPDATE in
transaction 2 cannot see the update made by transaction 1. Why do these two
statements show different effects when exposed to the influence of
transaction 1?

Another case:
/* init */ CREATE TABLE t0 (c1 INT PRIMARY KEY, c2 INT);
/* init */ INSERT INTO t0 (c1, c2) VALUES (0, 0);

 -- TRANSACTION 1;
BEGIN;
UPDATE t0 SET c1 = 5, c2 = 5 WHERE c2 = 0;
COMMIT;

 -- TRANSACTION 2;
BEGIN;
INSERT INTO t0 (c1, c2) VALUES (2, 2);
INSERT INTO t0 (c1, c2) VALUES (6, 12);
UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 0;
COMMIT;

-- Submit Order
/* T1 */ BEGIN;
/* T1 */ UPDATE t0 SET c1 = 5, c2 = 5 WHERE c2 = 0;
/* T2 */ BEGIN;
/* T2 */ INSERT INTO t0 (c1, c2) VALUES (2, 2);
/* T1 */ COMMIT;
/* T2 */ INSERT INTO t0 (c1, c2) VALUES (6, 12);
/* T2 */ UPDATE t0 SET c1 = 9, c2 = 9 WHERE c2 = 0;
-- ERROR:  could not serialize access due to concurrent update
/* T2 */ COMMIT;

SELECT * FROM t0;

 c1 | c2
----+----
  5 |  5

Although transaction 1 has been committed,  the UPDATE in transaction 2
still conflicts with transaction 1 and reports an error "could not serialize
access due to concurrent update". Why was transaction 2 affected by the
UPDATE in transaction 1 at this time?