BUG #16454: Mixed isolation levels inside transactions

The following bug has been logged on the website:

Bug reference:      16454
Logged by:          Lloyd Albin
Email address:      lalbin@scharp.org
PostgreSQL version: 12.2
Operating system:   Linux
Description:

I am seeing mixed/hybrid Isolation Levels when setting the isolation level
to SERIALIZABLE READ ONLY DEFERRABLE, this also happens with REPEATABLE READ
READ ONLY. The user tables sees only the data committed before the
transaction begins but the system tables sees data committed by other
transactions after the transaction begins. This means in my example the user
tables are Serializable Isolation Level and the system tables are Read
Committed Isolation Level.

I have testing this with PostgreSQL 10.6 & 12.2. The following reproducible
test case.

-- Writer Connection

CREATE EXTENSION pageinspect;
BEGIN;
CREATE SCHEMA test;
CREATE TABLE test.table ( test TEXT );
INSERT INTO test.table (test) VALUES ('row 1');
COMMIT;

-- Reader Connection

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT * FROM test.table;
test
-------
row 1
(1 row)
SELECT txid_current();
txid_current
--------------
349902815
(1 row)
SELECT oid, nspname FROM pg_catalog.pg_namespace WHERE nspname = 'test';
    oid    | nspname
-----------+---------
 513537401 | test
(1 row)
-- Use this OID for the next Query
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_oid
FROM heap_page_items(get_raw_page('pg_catalog.pg_namespace', 0))
WHERE t_oid = '(replace with saved OID)';
 tuple |  t_xmin   |  t_xmax   | t_cid | t_ctid |   t_oid
-------+-----------+-----------+-------+--------+-----------
    13 | 349902806 |         0 |     0 | (0,13) | 513537401
(1 row) 

-- Writer Connection

BEGIN;
CREATE SCHEMA new_test;
CREATE TABLE new_test.table (test TEXT);
INSERT INTO new_test.table VALUES ('row 2');
ALTER SCHEMA test RENAME TO old_test;
ALTER SCHEMA new_test RENAME TO test;
COMMIT;

-- Reader Connection

SELECT * FROM test.table;
test
------
(0 rows)
SELECT * FROM old_test.table;
test
-------
row 1
(1 row)
SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid, t_oid
FROM heap_page_items(get_raw_page('pg_catalog.pg_namespace', 0))
WHERE t_oid = '(replace with saved OID)';
 tuple |  t_xmin   |  t_xmax   | t_cid | t_ctid |   t_oid
-------+-----------+-----------+-------+--------+-----------
    13 | 349902806 | 349902827 |     7 | (0,15) | 513537401
    15 | 349902827 |         0 |     7 | (0,15) | 513537401
(2 rows) 

In the second half of the Reader Connection, I queried test.table and got 0
results. PostgreSQL actually queried the new table that was created after
the Reader's transaction started. When querying old_test.table, which did
not exist before the Reader's transaction started, I got the results I would
have expected from test.table. This means that the query used the wrong data
row from pg_catalog.pg_namespace. It used a row with an xmin greater than
the current txid. This means that the query used the Read Committed
Isolation Level when talking to the system tables instead of the isolation
level specified in the transaction causing a mixed/hybrid isolation level to
actually be used. I don't see any documentation talking about a mixed/hybrid
isolation level being implemented, so I am presuming this is a bug.


Lloyd Albin
Principal Database Administrator
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)