diff options
Diffstat (limited to 'contrib/amcheck/sql')
| -rw-r--r-- | contrib/amcheck/sql/check.sql | 1 | ||||
| -rw-r--r-- | contrib/amcheck/sql/check_btree.sql | 146 | ||||
| -rw-r--r-- | contrib/amcheck/sql/check_heap.sql | 147 |
3 files changed, 294 insertions, 0 deletions
diff --git a/contrib/amcheck/sql/check.sql b/contrib/amcheck/sql/check.sql new file mode 100644 index 0000000..5b3e6d5 --- /dev/null +++ b/contrib/amcheck/sql/check.sql @@ -0,0 +1 @@ +CREATE EXTENSION amcheck; diff --git a/contrib/amcheck/sql/check_btree.sql b/contrib/amcheck/sql/check_btree.sql new file mode 100644 index 0000000..033c04b --- /dev/null +++ b/contrib/amcheck/sql/check_btree.sql @@ -0,0 +1,146 @@ +CREATE TABLE bttest_a(id int8); +CREATE TABLE bttest_b(id int8); +CREATE TABLE bttest_multi(id int8, data int8); +CREATE TABLE delete_test_table (a bigint, b bigint, c bigint, d bigint); + +-- Stabalize tests +ALTER TABLE bttest_a SET (autovacuum_enabled = false); +ALTER TABLE bttest_b SET (autovacuum_enabled = false); +ALTER TABLE bttest_multi SET (autovacuum_enabled = false); +ALTER TABLE delete_test_table SET (autovacuum_enabled = false); + +INSERT INTO bttest_a SELECT * FROM generate_series(1, 100000); +INSERT INTO bttest_b SELECT * FROM generate_series(100000, 1, -1); +INSERT INTO bttest_multi SELECT i, i%2 FROM generate_series(1, 100000) as i; + +CREATE INDEX bttest_a_idx ON bttest_a USING btree (id) WITH (deduplicate_items = ON); +CREATE INDEX bttest_b_idx ON bttest_b USING btree (id); +CREATE UNIQUE INDEX bttest_multi_idx ON bttest_multi +USING btree (id) INCLUDE (data); + +CREATE ROLE regress_bttest_role; + +-- verify permissions are checked (error due to function not callable) +SET ROLE regress_bttest_role; +SELECT bt_index_check('bttest_a_idx'::regclass); +SELECT bt_index_parent_check('bttest_a_idx'::regclass); +RESET ROLE; + +-- we, intentionally, don't check relation permissions - it's useful +-- to run this cluster-wide with a restricted account, and as tested +-- above explicit permission has to be granted for that. +GRANT EXECUTE ON FUNCTION bt_index_check(regclass) TO regress_bttest_role; +GRANT EXECUTE ON FUNCTION bt_index_parent_check(regclass) TO regress_bttest_role; +GRANT EXECUTE ON FUNCTION bt_index_check(regclass, boolean) TO regress_bttest_role; +GRANT EXECUTE ON FUNCTION bt_index_parent_check(regclass, boolean) TO regress_bttest_role; +SET ROLE regress_bttest_role; +SELECT bt_index_check('bttest_a_idx'); +SELECT bt_index_parent_check('bttest_a_idx'); +RESET ROLE; + +-- verify plain tables are rejected (error) +SELECT bt_index_check('bttest_a'); +SELECT bt_index_parent_check('bttest_a'); + +-- verify non-existing indexes are rejected (error) +SELECT bt_index_check(17); +SELECT bt_index_parent_check(17); + +-- verify wrong index types are rejected (error) +BEGIN; +CREATE INDEX bttest_a_brin_idx ON bttest_a USING brin(id); +SELECT bt_index_parent_check('bttest_a_brin_idx'); +ROLLBACK; + +-- normal check outside of xact +SELECT bt_index_check('bttest_a_idx'); +-- more expansive tests +SELECT bt_index_check('bttest_a_idx', true); +SELECT bt_index_parent_check('bttest_b_idx', true); + +BEGIN; +SELECT bt_index_check('bttest_a_idx'); +SELECT bt_index_parent_check('bttest_b_idx'); +-- make sure we don't have any leftover locks +SELECT * FROM pg_locks +WHERE relation = ANY(ARRAY['bttest_a', 'bttest_a_idx', 'bttest_b', 'bttest_b_idx']::regclass[]) + AND pid = pg_backend_pid(); +COMMIT; + +-- Deduplication +TRUNCATE bttest_a; +INSERT INTO bttest_a SELECT 42 FROM generate_series(1, 2000); +SELECT bt_index_check('bttest_a_idx', true); + +-- normal check outside of xact for index with included columns +SELECT bt_index_check('bttest_multi_idx'); +-- more expansive tests for index with included columns +SELECT bt_index_parent_check('bttest_multi_idx', true, true); + +-- repeat expansive tests for index built using insertions +TRUNCATE bttest_multi; +INSERT INTO bttest_multi SELECT i, i%2 FROM generate_series(1, 100000) as i; +SELECT bt_index_parent_check('bttest_multi_idx', true, true); + +-- +-- Test for multilevel page deletion/downlink present checks, and rootdescend +-- checks +-- +INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,80000) i; +ALTER TABLE delete_test_table ADD PRIMARY KEY (a,b,c,d); +-- Delete most entries, and vacuum, deleting internal pages and creating "fast +-- root" +DELETE FROM delete_test_table WHERE a < 79990; +VACUUM delete_test_table; +SELECT bt_index_parent_check('delete_test_table_pkey', true); + +-- +-- BUG #15597: must not assume consistent input toasting state when forming +-- tuple. Bloom filter must fingerprint normalized index tuple representation. +-- +CREATE TABLE toast_bug(buggy text); +ALTER TABLE toast_bug ALTER COLUMN buggy SET STORAGE extended; +CREATE INDEX toasty ON toast_bug(buggy); + +-- pg_attribute entry for toasty.buggy (the index) will have plain storage: +UPDATE pg_attribute SET attstorage = 'p' +WHERE attrelid = 'toasty'::regclass AND attname = 'buggy'; + +-- Whereas pg_attribute entry for toast_bug.buggy (the table) still has extended storage: +SELECT attstorage FROM pg_attribute +WHERE attrelid = 'toast_bug'::regclass AND attname = 'buggy'; + +-- Insert compressible heap tuple (comfortably exceeds TOAST_TUPLE_THRESHOLD): +INSERT INTO toast_bug SELECT repeat('a', 2200); +-- Should not get false positive report of corruption: +SELECT bt_index_check('toasty', true); + +-- +-- Check that index expressions and predicates are run as the table's owner +-- +TRUNCATE bttest_a; +INSERT INTO bttest_a SELECT * FROM generate_series(1, 1000); +ALTER TABLE bttest_a OWNER TO regress_bttest_role; +-- A dummy index function checking current_user +CREATE FUNCTION ifun(int8) RETURNS int8 AS $$ +BEGIN + ASSERT current_user = 'regress_bttest_role', + format('ifun(%s) called by %s', $1, current_user); + RETURN $1; +END; +$$ LANGUAGE plpgsql IMMUTABLE; + +CREATE INDEX bttest_a_expr_idx ON bttest_a ((ifun(id) + ifun(0))) + WHERE ifun(id + 10) > ifun(10); + +SELECT bt_index_check('bttest_a_expr_idx', true); + +-- cleanup +DROP TABLE bttest_a; +DROP TABLE bttest_b; +DROP TABLE bttest_multi; +DROP TABLE delete_test_table; +DROP TABLE toast_bug; +DROP FUNCTION ifun(int8); +DROP OWNED BY regress_bttest_role; -- permissions +DROP ROLE regress_bttest_role; diff --git a/contrib/amcheck/sql/check_heap.sql b/contrib/amcheck/sql/check_heap.sql new file mode 100644 index 0000000..1745bae --- /dev/null +++ b/contrib/amcheck/sql/check_heap.sql @@ -0,0 +1,147 @@ +CREATE TABLE heaptest (a integer, b text); +REVOKE ALL ON heaptest FROM PUBLIC; + +-- Check that invalid skip option is rejected +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'rope'); + +-- Check specifying invalid block ranges when verifying an empty table +SELECT * FROM verify_heapam(relation := 'heaptest', startblock := 0, endblock := 0); +SELECT * FROM verify_heapam(relation := 'heaptest', startblock := 5, endblock := 8); + +-- Check that valid options are not rejected nor corruption reported +-- for an empty table, and that skip enum-like parameter is case-insensitive +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'none'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'all-frozen'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'all-visible'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'None'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'All-Frozen'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'All-Visible'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'NONE'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'ALL-FROZEN'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'ALL-VISIBLE'); + + +-- Add some data so subsequent tests are not entirely trivial +INSERT INTO heaptest (a, b) + (SELECT gs, repeat('x', gs) + FROM generate_series(1,50) gs); + +-- pg_stat_io test: +-- verify_heapam always uses a BAS_BULKREAD BufferAccessStrategy, whereas a +-- sequential scan does so only if the table is large enough when compared to +-- shared buffers (see initscan()). CREATE DATABASE ... also unconditionally +-- uses a BAS_BULKREAD strategy, but we have chosen to use a tablespace and +-- verify_heapam to provide coverage instead of adding another expensive +-- operation to the main regression test suite. +-- +-- Create an alternative tablespace and move the heaptest table to it, causing +-- it to be rewritten and all the blocks to reliably evicted from shared +-- buffers -- guaranteeing actual reads when we next select from it in the +-- same transaction. The heaptest table is smaller than the default +-- wal_skip_threshold, so a wal_level=minimal commit reads the table into +-- shared_buffers. A transaction delays that and excludes any autovacuum. +SET allow_in_place_tablespaces = true; +CREATE TABLESPACE regress_test_stats_tblspc LOCATION ''; +SELECT sum(reads) AS stats_bulkreads_before + FROM pg_stat_io WHERE context = 'bulkread' \gset +BEGIN; +ALTER TABLE heaptest SET TABLESPACE regress_test_stats_tblspc; +-- Check that valid options are not rejected nor corruption reported +-- for a non-empty table +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'none'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'all-frozen'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'all-visible'); +SELECT * FROM verify_heapam(relation := 'heaptest', startblock := 0, endblock := 0); +COMMIT; + +-- verify_heapam should have read in the page written out by +-- ALTER TABLE ... SET TABLESPACE ... +-- causing an additional bulkread, which should be reflected in pg_stat_io. +SELECT pg_stat_force_next_flush(); +SELECT sum(reads) AS stats_bulkreads_after + FROM pg_stat_io WHERE context = 'bulkread' \gset +SELECT :stats_bulkreads_after > :stats_bulkreads_before; + +CREATE ROLE regress_heaptest_role; + +-- verify permissions are checked (error due to function not callable) +SET ROLE regress_heaptest_role; +SELECT * FROM verify_heapam(relation := 'heaptest'); +RESET ROLE; + +GRANT EXECUTE ON FUNCTION verify_heapam(regclass, boolean, boolean, text, bigint, bigint) TO regress_heaptest_role; + +-- verify permissions are now sufficient +SET ROLE regress_heaptest_role; +SELECT * FROM verify_heapam(relation := 'heaptest'); +RESET ROLE; + +-- Check specifying invalid block ranges when verifying a non-empty table. +SELECT * FROM verify_heapam(relation := 'heaptest', startblock := 0, endblock := 10000); +SELECT * FROM verify_heapam(relation := 'heaptest', startblock := 10000, endblock := 11000); + +-- Vacuum freeze to change the xids encountered in subsequent tests +VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) heaptest; + +-- Check that valid options are not rejected nor corruption reported +-- for a non-empty frozen table +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'none'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'all-frozen'); +SELECT * FROM verify_heapam(relation := 'heaptest', skip := 'all-visible'); +SELECT * FROM verify_heapam(relation := 'heaptest', startblock := 0, endblock := 0); + +-- Check that partitioned tables (the parent ones) which don't have visibility +-- maps are rejected +CREATE TABLE test_partitioned (a int, b text default repeat('x', 5000)) + PARTITION BY list (a); +SELECT * FROM verify_heapam('test_partitioned', + startblock := NULL, + endblock := NULL); + +-- Check that valid options are not rejected nor corruption reported +-- for an empty partition table (the child one) +CREATE TABLE test_partition partition OF test_partitioned FOR VALUES IN (1); +SELECT * FROM verify_heapam('test_partition', + startblock := NULL, + endblock := NULL); + +-- Check that valid options are not rejected nor corruption reported +-- for a non-empty partition table (the child one) +INSERT INTO test_partitioned (a) (SELECT 1 FROM generate_series(1,1000) gs); +SELECT * FROM verify_heapam('test_partition', + startblock := NULL, + endblock := NULL); + +-- Check that indexes are rejected +CREATE INDEX test_index ON test_partition (a); +SELECT * FROM verify_heapam('test_index', + startblock := NULL, + endblock := NULL); + +-- Check that views are rejected +CREATE VIEW test_view AS SELECT 1; +SELECT * FROM verify_heapam('test_view', + startblock := NULL, + endblock := NULL); + +-- Check that sequences are rejected +CREATE SEQUENCE test_sequence; +SELECT * FROM verify_heapam('test_sequence', + startblock := NULL, + endblock := NULL); + +-- Check that foreign tables are rejected +CREATE FOREIGN DATA WRAPPER dummy; +CREATE SERVER dummy_server FOREIGN DATA WRAPPER dummy; +CREATE FOREIGN TABLE test_foreign_table () SERVER dummy_server; +SELECT * FROM verify_heapam('test_foreign_table', + startblock := NULL, + endblock := NULL); + +-- cleanup +DROP TABLE heaptest; +DROP TABLESPACE regress_test_stats_tblspc; +DROP TABLE test_partition; +DROP TABLE test_partitioned; +DROP OWNED BY regress_heaptest_role; -- permissions +DROP ROLE regress_heaptest_role; |