From 5456cf819426d3f90c004f673dfc863903e568a1 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sat, 22 Oct 2022 12:47:33 +0200 Subject: [PATCH 1/2] pageinspect brinbugs test --- contrib/pageinspect/Makefile | 2 +- contrib/pageinspect/expected/brinbugs.out | 222 ++++++++++++++++++++++ contrib/pageinspect/sql/brinbugs.sql | 114 +++++++++++ 3 files changed, 337 insertions(+), 1 deletion(-) 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 5c0736564ab..92305e981f7 100644 --- a/contrib/pageinspect/Makefile +++ b/contrib/pageinspect/Makefile @@ -21,7 +21,7 @@ DATA = pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.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); -- 2.37.3