From 033286cff9733c24fdc7c3f774d947c9f1072aa0 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sun, 8 Jan 2023 23:42:41 +0100 Subject: [PATCH] extra tests --- contrib/pageinspect/Makefile | 2 +- contrib/pageinspect/expected/brin-fails.out | 152 ++++++++++++++++++++ contrib/pageinspect/sql/brin-fails.sql | 85 +++++++++++ 3 files changed, 238 insertions(+), 1 deletion(-) create mode 100644 contrib/pageinspect/expected/brin-fails.out create mode 100644 contrib/pageinspect/sql/brin-fails.sql diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile index 95e030b396..69a28a6d3d 100644 --- a/contrib/pageinspect/Makefile +++ b/contrib/pageinspect/Makefile @@ -22,7 +22,7 @@ DATA = pageinspect--1.11--1.12.sql 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 brin-fails ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/contrib/pageinspect/expected/brin-fails.out b/contrib/pageinspect/expected/brin-fails.out new file mode 100644 index 0000000000..a12c761fc8 --- /dev/null +++ b/contrib/pageinspect/expected/brin-fails.out @@ -0,0 +1,152 @@ +create table t (a int); +create extension pageinspect; +-- works +drop index if exists t_a_idx; +NOTICE: index "t_a_idx" does not exist, skipping +truncate t; +insert into t values (null), (1); +create index on t using brin (a); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} +(1 row) + +-- works +drop index if exists t_a_idx; +truncate t; +insert into t values (1), (null); +create index on t using brin (a); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} +(1 row) + +-- fails +drop index if exists t_a_idx; +truncate t; +insert into t values (null); +create index on t using brin (a); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} +(1 row) + +-- fails +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a); +insert into t values (null); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | f | t | f | {1 .. 1} +(1 row) + +-- works +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null), (1); +select brin_summarize_new_values('t_a_idx'); + brin_summarize_new_values +--------------------------- + 1 +(1 row) + +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | t | f | f | + 2 | 1 | 1 | f | t | f | {1 .. 1} +(2 rows) + +-- fails +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null); +select brin_summarize_new_values('t_a_idx'); + brin_summarize_new_values +--------------------------- + 1 +(1 row) + +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | t | f | f | + 2 | 1 | 1 | f | t | f | {1 .. 1} +(2 rows) + +-- works +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null); +insert into t values (1); +select brin_summarize_new_values('t_a_idx'); + brin_summarize_new_values +--------------------------- + 1 +(1 row) + +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | t | f | f | + 2 | 1 | 1 | f | t | f | {1 .. 1} +(2 rows) + +-- fails +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null); +insert into t values (null); +select brin_summarize_new_values('t_a_idx'); + brin_summarize_new_values +--------------------------- + 1 +(1 row) + +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | t | f | f | + 2 | 1 | 1 | f | t | f | {1 .. 1} +(2 rows) + +-- fails +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null); +insert into t values (null); +select brin_summarize_new_values('t_a_idx'); + brin_summarize_new_values +--------------------------- + 1 +(1 row) + +insert into t values (null); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value +------------+--------+--------+----------+----------+-------------+---------- + 1 | 0 | 1 | t | f | f | + 2 | 1 | 1 | f | t | f | {1 .. 1} +(2 rows) + diff --git a/contrib/pageinspect/sql/brin-fails.sql b/contrib/pageinspect/sql/brin-fails.sql new file mode 100644 index 0000000000..ca57ba7e03 --- /dev/null +++ b/contrib/pageinspect/sql/brin-fails.sql @@ -0,0 +1,85 @@ +create table t (a int); +create extension pageinspect; + +-- works +drop index if exists t_a_idx; +truncate t; +insert into t values (null), (1); +create index on t using brin (a); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + +-- works +drop index if exists t_a_idx; +truncate t; +insert into t values (1), (null); +create index on t using brin (a); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + +-- fails +drop index if exists t_a_idx; +truncate t; +insert into t values (null); +create index on t using brin (a); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + +-- fails +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a); +insert into t values (null); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + +-- works +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null), (1); +select brin_summarize_new_values('t_a_idx'); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + +-- fails +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null); +select brin_summarize_new_values('t_a_idx'); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + +-- works +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null); +insert into t values (1); +select brin_summarize_new_values('t_a_idx'); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + +-- fails +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null); +insert into t values (null); +select brin_summarize_new_values('t_a_idx'); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); + +-- fails +drop index if exists t_a_idx; +truncate t; +create index on t using brin (a) with (pages_per_range=1); +insert into t select null from generate_series(1,291); -- fill first page +insert into t values (null); +insert into t values (null); +select brin_summarize_new_values('t_a_idx'); +insert into t values (null); +insert into t values (1); +select * from brin_page_items(get_raw_page('t_a_idx', 2), 't_a_idx'::regclass); -- 2.39.0