# Multi-level GiST predicate locking under SERIALIZABLE # # When a SERIALIZABLE GiST scan prunes a child subtree at an internal # page, gistScanPage() locks only that internal page and not the child leaf # page. The first concurrent insert into that subtree widens the internal # downlink in place (checking CheckForSerializableConflictIn on the internal # page), after which a second concurrent insert covered by that widened # downlink sees gistgetadjusted() == NULL on the internal page and descends # directly to the leaf page without checking CheckForSerializableConflictIn # on the internal page. setup { CREATE TABLE bookings (id int, span int4range); INSERT INTO bookings SELECT g, int4range(g * 10, g * 10 + 5) FROM generate_series(1, 300) AS g; CREATE INDEX bookings_span_gist_idx ON bookings USING gist (span); CREATE INDEX bookings_id_btree_idx ON bookings (id); } teardown { DROP TABLE bookings; } session s1 setup { BEGIN ISOLATION LEVEL SERIALIZABLE; SET enable_seqscan = off; SET enable_bitmapscan = off; } step r1_bt { SELECT count(*) FROM bookings WHERE id = 5000; } step w1_bt { INSERT INTO bookings VALUES (5000, '[1, 2)'); } step r1_leaf { SELECT count(*) FROM bookings WHERE span && '[15, 20)'::int4range; } step w1_leaf { INSERT INTO bookings VALUES (501, '[15, 20)'); } step r1_hi { SELECT count(*) FROM bookings WHERE span && '[5000, 5005)'::int4range; } step r1_lo { SELECT count(*) FROM bookings WHERE span && '[4000, 4005)'::int4range; } step w1_hi { INSERT INTO bookings VALUES (502, '[5000, 5005)'); } step c1 { COMMIT; } session s2 setup { BEGIN ISOLATION LEVEL SERIALIZABLE; SET enable_seqscan = off; SET enable_bitmapscan = off; } step r2_bt { SELECT count(*) FROM bookings WHERE id = 5000; } step w2_bt { INSERT INTO bookings VALUES (5000, '[3, 4)'); } step r2_leaf { SELECT count(*) FROM bookings WHERE span && '[15, 20)'::int4range; } step w2_leaf { INSERT INTO bookings VALUES (503, '[15, 20)'); } step r2_hi { SELECT count(*) FROM bookings WHERE span && '[5000, 5005)'::int4range; } step w2_hi { INSERT INTO bookings VALUES (504, '[5000, 5005)'); } step w2_lo { INSERT INTO bookings VALUES (505, '[4000, 4005)'); } step c2 { COMMIT; } step check_rows { SELECT id, span FROM bookings WHERE id >= 500 ORDER BY id; } # Control 1: B-tree scan on absent key 5000 aborts s2 with SQLSTATE 40001. permutation r1_bt r2_bt w1_bt w2_bt c1 c2 check_rows # Control 2: GiST scan on absent range [15, 20) inside root downlink descends # to the leaf page and aborts s2 with SQLSTATE 40001. permutation r1_leaf r2_leaf w1_leaf w2_leaf c1 c2 check_rows # Bug 1 (phantom): both GiST scans prune at the internal root page; both commit. permutation r1_hi r2_hi w1_hi w2_hi c1 c2 check_rows # Bug 2 (write skew): s1 reads [4000, 4005) and inserts [5000, 5005) (widening # the root downlink across [4000, 4005) as well); s2 reads [5000, 5005) and # inserts [4000, 4005) without modifying the root, so both commit. permutation r1_lo r2_hi w1_hi w2_lo c1 c2 check_rows