From dea5e7aa821ddf745e509371f33bf1953ff6e853 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sat, 22 Oct 2022 12:47:33 +0200 Subject: [PATCH 1/3] pageinspect brinbugs test Introduce a brinbugs.sql test suite into pageinspect, demonstrating the issue with forgetting about initial NULL values. Ultimately this should be added to the exisging brin.sql suite. Furthermore, this tweaks an existing isolation test, originally intended to test concurrent inserts and summarization, to also test this - it's enough to ensure the first value added to the table is NULL. --- contrib/pageinspect/Makefile | 2 +- contrib/pageinspect/expected/brinbugs.out | 222 ++++++++++++++++++ contrib/pageinspect/sql/brinbugs.sql | 114 +++++++++ ...summarization-and-inprogress-insertion.out | 6 +- ...ummarization-and-inprogress-insertion.spec | 1 + 5 files changed, 341 insertions(+), 4 deletions(-) create mode 100644 contrib/pageinspect/expected/brinbugs.out create mode 100644 contrib/pageinspect/sql/brinbugs.sql diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile index ad5a3ac5112..67eb02b78fd 100644 --- a/contrib/pageinspect/Makefile +++ b/contrib/pageinspect/Makefile @@ -22,7 +22,7 @@ DATA = pageinspect--1.10--1.11.sql \ pageinspect--1.0--1.1.sql PGFILEDESC = "pageinspect - functions to inspect contents of database pages" -REGRESS = page btree brin gin gist hash checksum oldextversions +REGRESS = page btree brin gin gist hash checksum oldextversions brinbugs ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/contrib/pageinspect/expected/brinbugs.out b/contrib/pageinspect/expected/brinbugs.out new file mode 100644 index 00000000000..23843caa138 --- /dev/null +++ b/contrib/pageinspect/expected/brinbugs.out @@ -0,0 +1,222 @@ +create extension pageinspect; +create table t (a int, b int); +create index on t using brin (a, b); +-- both columns should have has_nulls=false and [1,1] range +truncate t; +insert into t values (1,1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | f | f | {1 .. 1} + 1 | 0 | 2 | f | f | f | {1 .. 1} +(2 rows) + +-- first column should have all_nulls=true, second has_nulls=false and [1,1] range +truncate t; +insert into t values (null, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | t | f | f | + 1 | 0 | 2 | f | f | f | {1 .. 1} +(2 rows) + +-- both columns should have all_nulls=true +truncate t; +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+------- + 1 | 0 | 1 | t | f | f | + 1 | 0 | 2 | t | f | f | +(2 rows) + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (null, null); +insert into t values (1, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} + 1 | 0 | 2 | f | t | f | {1 .. 1} +(2 rows) + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (1, 1); +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} + 1 | 0 | 2 | f | t | f | {1 .. 1} +(2 rows) + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (null, 1); +insert into t values (1, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} + 1 | 0 | 2 | f | t | f | {1 .. 1} +(2 rows) + +-- both columns should have has_nulls=false and [1,1] range +truncate t; +insert into t values (1, 1); +insert into t values (1, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | f | f | {1 .. 1} + 1 | 0 | 2 | f | f | f | {1 .. 1} +(2 rows) + +-- both columns should have all_nulls=true only +truncate t; +insert into t values (null, null); +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+------- + 1 | 0 | 1 | t | f | f | + 1 | 0 | 2 | t | f | f | +(2 rows) + +-- first column should have has_nulls=false and [1,1] range, second all_nulls=true +truncate t; +insert into t values (1, null); +insert into t values (1, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | f | f | {1 .. 1} + 1 | 0 | 2 | t | f | f | +(2 rows) + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+------- + 1 | 0 | 1 | t | f | f | + 1 | 0 | 2 | t | f | f | +(2 rows) + +insert into t values (1,1); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} + 1 | 0 | 2 | f | t | f | {1 .. 1} +(2 rows) + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (1, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | f | f | {1 .. 1} + 1 | 0 | 2 | f | f | f | {1 .. 1} +(2 rows) + +insert into t values (null, null); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} + 1 | 0 | 2 | f | t | f | {1 .. 1} +(2 rows) + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (null, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | t | f | f | + 1 | 0 | 2 | f | f | f | {1 .. 1} +(2 rows) + +insert into t values (1, null); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} + 1 | 0 | 2 | f | t | f | {1 .. 1} +(2 rows) + +-- both columns should have has_nulls=false and [1,1] range +truncate t; +insert into t values (1, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | f | f | {1 .. 1} + 1 | 0 | 2 | f | f | f | {1 .. 1} +(2 rows) + +insert into t values (1, 1); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | f | f | {1 .. 1} + 1 | 0 | 2 | f | f | f | {1 .. 1} +(2 rows) + +-- both columns should have all_nulls=true +truncate t; +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+------- + 1 | 0 | 1 | t | f | f | + 1 | 0 | 2 | t | f | f | +(2 rows) + +insert into t values (null, null); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+------- + 1 | 0 | 1 | t | f | f | + 1 | 0 | 2 | t | f | f | +(2 rows) + +-- first column should have has_nulls=false and [1,1] range, second all_nulls=true +truncate t; +insert into t values (1, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | f | f | {1 .. 1} + 1 | 0 | 2 | t | f | f | +(2 rows) + +insert into t values (1, null); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | f | f | {1 .. 1} + 1 | 0 | 2 | t | f | f | +(2 rows) + diff --git a/contrib/pageinspect/sql/brinbugs.sql b/contrib/pageinspect/sql/brinbugs.sql new file mode 100644 index 00000000000..a141aed5adc --- /dev/null +++ b/contrib/pageinspect/sql/brinbugs.sql @@ -0,0 +1,114 @@ +create extension pageinspect; + +create table t (a int, b int); +create index on t using brin (a, b); + + +-- both columns should have has_nulls=false and [1,1] range +truncate t; +insert into t values (1,1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- first column should have all_nulls=true, second has_nulls=false and [1,1] range +truncate t; +insert into t values (null, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have all_nulls=true +truncate t; +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (null, null); +insert into t values (1, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (1, 1); +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (null, 1); +insert into t values (1, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have has_nulls=false and [1,1] range +truncate t; +insert into t values (1, 1); +insert into t values (1, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have all_nulls=true only +truncate t; +insert into t values (null, null); +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- first column should have has_nulls=false and [1,1] range, second all_nulls=true +truncate t; +insert into t values (1, null); +insert into t values (1, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); +insert into t values (1,1); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (1, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); +insert into t values (null, null); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have has_nulls=true and [1,1] range +truncate t; +insert into t values (null, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); +insert into t values (1, null); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have has_nulls=false and [1,1] range +truncate t; +insert into t values (1, 1); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); +insert into t values (1, 1); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- both columns should have all_nulls=true +truncate t; +insert into t values (null, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); +insert into t values (null, null); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); + +-- first column should have has_nulls=false and [1,1] range, second all_nulls=true +truncate t; +insert into t values (1, null); +vacuum full t; +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); +insert into t values (1, null); +select * from brin_page_items(get_raw_page('t_a_b_idx', 2), 't_a_b_idx'::regclass); diff --git a/src/test/modules/brin/expected/summarization-and-inprogress-insertion.out b/src/test/modules/brin/expected/summarization-and-inprogress-insertion.out index 2a4755d0998..02ef52d299a 100644 --- a/src/test/modules/brin/expected/summarization-and-inprogress-insertion.out +++ b/src/test/modules/brin/expected/summarization-and-inprogress-insertion.out @@ -4,7 +4,7 @@ starting permutation: s2check s1b s2b s1i s2summ s1c s2c s2check step s2check: SELECT * FROM brin_page_items(get_raw_page('brinidx', 2), 'brinidx'::regclass); itemoffset|blknum|attnum|allnulls|hasnulls|placeholder|value ----------+------+------+--------+--------+-----------+-------- - 1| 0| 1|f |f |f |{1 .. 1} + 1| 0| 1|f |t |f |{1 .. 1} (1 row) step s1b: BEGIN ISOLATION LEVEL REPEATABLE READ; @@ -26,7 +26,7 @@ step s2c: COMMIT; step s2check: SELECT * FROM brin_page_items(get_raw_page('brinidx', 2), 'brinidx'::regclass); itemoffset|blknum|attnum|allnulls|hasnulls|placeholder|value ----------+------+------+--------+--------+-----------+----------- - 1| 0| 1|f |f |f |{1 .. 1} + 1| 0| 1|f |t |f |{1 .. 1} 2| 1| 1|f |f |f |{1 .. 1000} (2 rows) @@ -35,7 +35,7 @@ starting permutation: s2check s1b s1i s2vacuum s1c s2check step s2check: SELECT * FROM brin_page_items(get_raw_page('brinidx', 2), 'brinidx'::regclass); itemoffset|blknum|attnum|allnulls|hasnulls|placeholder|value ----------+------+------+--------+--------+-----------+-------- - 1| 0| 1|f |f |f |{1 .. 1} + 1| 0| 1|f |t |f |{1 .. 1} (1 row) step s1b: BEGIN ISOLATION LEVEL REPEATABLE READ; diff --git a/src/test/modules/brin/specs/summarization-and-inprogress-insertion.spec b/src/test/modules/brin/specs/summarization-and-inprogress-insertion.spec index 19ac18a2e88..18ba92b7ba1 100644 --- a/src/test/modules/brin/specs/summarization-and-inprogress-insertion.spec +++ b/src/test/modules/brin/specs/summarization-and-inprogress-insertion.spec @@ -9,6 +9,7 @@ setup ) WITH (fillfactor=10); CREATE INDEX brinidx ON brin_iso USING brin (value) WITH (pages_per_range=1); -- this fills the first page + INSERT INTO brin_iso VALUES (NULL); DO $$ DECLARE curtid tid; BEGIN -- 2.38.1