>From 7f04ac6255360f21dcba0323630fbb4b85bb6cab Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Wed, 4 Jun 2014 21:36:19 +0200 Subject: [PATCH 3/3] Add tests for interaction between visibility and CREATE INDEX's heap scan. There have been several bugs over the years in the interaction between HeapTupleSatisfiesVacuum() and CREATE INDEX. It's also looking like there will be some changes to the visiblity routines expected during the 9.5 cycle. Those facts together seem to warrant a couple of tests, even if they aren't exactly pretty. --- src/test/regress/expected/create_index.out | 198 +++++++++++++++++++++++++++++ src/test/regress/sql/create_index.sql | 65 ++++++++++ 2 files changed, 263 insertions(+) diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index f6f5516..b9cd3d8 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2782,3 +2782,201 @@ explain (costs off) Index Cond: ((thousand = 1) AND (tenthous = 1001)) (2 rows) +---- +-- Check interactions of index creation in a xact that also has done +-- insertions, updates and deletions. There've been several bugs +-- around that. +---- +CREATE TABLE sametrans1(a text, b int default 1); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-del'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-del-after-index'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-main'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-sub-abort'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-sub'); +BEGIN; +INSERT INTO sametrans1(a) VALUES ('ins-mainxact'); +INSERT INTO sametrans1(a) VALUES ('ins-mainxact-subdel-abort'); +INSERT INTO sametrans1(a) VALUES ('ins-mainxact-subdel'); +INSERT INTO sametrans1(a) VALUES ('ins-mainxact-upd-sub-abort'); +INSERT INTO sametrans1(a) VALUES ('ins-mainxact-upd-sub'); +DELETE FROM sametrans1 WHERE a = 'ins-before-xact-del'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-main'; +SAVEPOINT a; +INSERT INTO sametrans1(a) VALUES ('ins-sub'); +INSERT INTO sametrans1(a) VALUES ('ins-sub-del-mainxact'); +DELETE FROM sametrans1 WHERE a = 'ins-mainxact-subdel'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-sub'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-mainxact-upd-sub'; +RELEASE SAVEPOINT a; +SAVEPOINT b; +INSERT INTO sametrans1(a) VALUES ('ins-sub-abort'); +DELETE FROM sametrans1 WHERE a = 'ins-mainxact-subdel-abort'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-sub-abort'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-mainxact-upd-sub-abort'; +ROLLBACK TO b; +-- don't add anything here, page pruning might otherwise remove rows +CREATE UNIQUE INDEX ON sametrans1 (a); +CREATE INDEX ON sametrans1 (b); +DELETE FROM sametrans1 WHERE a = 'ins-sub-del-mainxact'; +DELETE FROM sametrans1 WHERE a = 'ins-before-xact-del-after-index'; +COMMIT; +INSERT INTO sametrans1(a) VALUES ('ins-after-xact'); +-- force bitmapscans, they show the indexscans separately from the heap scan +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_indexonlyscan = off; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-before-xact'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-before-xact'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-del'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1) + Recheck Cond: (a = 'ins-before-xact-del'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-before-xact-del'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-del-after-index'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1) + Recheck Cond: (a = 'ins-before-xact-del-after-index'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-before-xact-del-after-index'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-main'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-before-xact-upd-main'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-before-xact-upd-main'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-sub-abort'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-before-xact-upd-sub-abort'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-before-xact-upd-sub-abort'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-sub'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-before-xact-upd-sub'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-before-xact-upd-sub'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-mainxact'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-mainxact'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-subdel-abort'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-mainxact-subdel-abort'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-mainxact-subdel-abort'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-subdel'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1) + Recheck Cond: (a = 'ins-mainxact-subdel'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-mainxact-subdel'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-upd-sub-abort'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-mainxact-upd-sub-abort'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-mainxact-upd-sub-abort'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-upd-sub'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-mainxact-upd-sub'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-mainxact-upd-sub'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-sub'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-sub'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub-del-mainxact'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1) + Recheck Cond: (a = 'ins-sub-del-mainxact'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-sub-del-mainxact'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub-abort'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=0 loops=1) + Recheck Cond: (a = 'ins-sub-abort'::text) + Heap Blocks: + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=0 loops=1) + Index Cond: (a = 'ins-sub-abort'::text) +(5 rows) + +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-after-xact'; + QUERY PLAN +--------------------------------------------------------------------- + Bitmap Heap Scan on sametrans1 (actual rows=1 loops=1) + Recheck Cond: (a = 'ins-after-xact'::text) + Heap Blocks: exact=1 + -> Bitmap Index Scan on sametrans1_a_idx (actual rows=1 loops=1) + Index Cond: (a = 'ins-after-xact'::text) +(5 rows) + +SET enable_seqscan = on; +SET enable_indexscan = on; +SET enable_indexonlyscan = on; +DROP TABLE sametrans1; diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index d4d24ef..1fae3e9 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -938,3 +938,68 @@ ORDER BY thousand; explain (costs off) select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null)); + +---- +-- Check interactions of index creation in a xact that also has done +-- insertions, updates and deletions. There've been several bugs +-- around that. +---- +CREATE TABLE sametrans1(a text, b int default 1); + +INSERT INTO sametrans1(a) VALUES ('ins-before-xact'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-del'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-del-after-index'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-main'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-sub-abort'); +INSERT INTO sametrans1(a) VALUES ('ins-before-xact-upd-sub'); +BEGIN; +INSERT INTO sametrans1(a) VALUES ('ins-mainxact'); +INSERT INTO sametrans1(a) VALUES ('ins-mainxact-subdel-abort'); +INSERT INTO sametrans1(a) VALUES ('ins-mainxact-subdel'); +INSERT INTO sametrans1(a) VALUES ('ins-mainxact-upd-sub-abort'); +INSERT INTO sametrans1(a) VALUES ('ins-mainxact-upd-sub'); +DELETE FROM sametrans1 WHERE a = 'ins-before-xact-del'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-main'; +SAVEPOINT a; +INSERT INTO sametrans1(a) VALUES ('ins-sub'); +INSERT INTO sametrans1(a) VALUES ('ins-sub-del-mainxact'); +DELETE FROM sametrans1 WHERE a = 'ins-mainxact-subdel'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-sub'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-mainxact-upd-sub'; +RELEASE SAVEPOINT a; +SAVEPOINT b; +INSERT INTO sametrans1(a) VALUES ('ins-sub-abort'); +DELETE FROM sametrans1 WHERE a = 'ins-mainxact-subdel-abort'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-before-xact-upd-sub-abort'; +UPDATE sametrans1 SET b = b + 1 WHERE a = 'ins-mainxact-upd-sub-abort'; +ROLLBACK TO b; +-- don't add anything here, page pruning might otherwise remove rows +CREATE UNIQUE INDEX ON sametrans1 (a); +CREATE INDEX ON sametrans1 (b); +DELETE FROM sametrans1 WHERE a = 'ins-sub-del-mainxact'; +DELETE FROM sametrans1 WHERE a = 'ins-before-xact-del-after-index'; +COMMIT; +INSERT INTO sametrans1(a) VALUES ('ins-after-xact'); +-- force bitmapscans, they show the indexscans separately from the heap scan +SET enable_seqscan = off; +SET enable_indexscan = off; +SET enable_indexonlyscan = off; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-del'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-del-after-index'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-main'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-sub-abort'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-before-xact-upd-sub'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-subdel-abort'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-subdel'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-upd-sub-abort'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-mainxact-upd-sub'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub-del-mainxact'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-sub-abort'; +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM sametrans1 WHERE a = 'ins-after-xact'; +SET enable_seqscan = on; +SET enable_indexscan = on; +SET enable_indexonlyscan = on; +DROP TABLE sametrans1; -- 2.0.0.rc2.4.g1dc51c6.dirty