summaryrefslogtreecommitdiffstats
path: root/contrib/amcheck/expected/check_btree.out
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/amcheck/expected/check_btree.out')
-rw-r--r--contrib/amcheck/expected/check_btree.out187
1 files changed, 187 insertions, 0 deletions
diff --git a/contrib/amcheck/expected/check_btree.out b/contrib/amcheck/expected/check_btree.out
new file mode 100644
index 0000000..13848b7
--- /dev/null
+++ b/contrib/amcheck/expected/check_btree.out
@@ -0,0 +1,187 @@
+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);
+ERROR: permission denied for function bt_index_check
+SELECT bt_index_parent_check('bttest_a_idx'::regclass);
+ERROR: permission denied for function bt_index_parent_check
+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');
+ bt_index_check
+----------------
+
+(1 row)
+
+SELECT bt_index_parent_check('bttest_a_idx');
+ bt_index_parent_check
+-----------------------
+
+(1 row)
+
+RESET ROLE;
+-- verify plain tables are rejected (error)
+SELECT bt_index_check('bttest_a');
+ERROR: "bttest_a" is not an index
+SELECT bt_index_parent_check('bttest_a');
+ERROR: "bttest_a" is not an index
+-- verify non-existing indexes are rejected (error)
+SELECT bt_index_check(17);
+ERROR: could not open relation with OID 17
+SELECT bt_index_parent_check(17);
+ERROR: could not open relation with OID 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');
+ERROR: only B-Tree indexes are supported as targets for verification
+DETAIL: Relation "bttest_a_brin_idx" is not a B-Tree index.
+ROLLBACK;
+-- normal check outside of xact
+SELECT bt_index_check('bttest_a_idx');
+ bt_index_check
+----------------
+
+(1 row)
+
+-- more expansive tests
+SELECT bt_index_check('bttest_a_idx', true);
+ bt_index_check
+----------------
+
+(1 row)
+
+SELECT bt_index_parent_check('bttest_b_idx', true);
+ bt_index_parent_check
+-----------------------
+
+(1 row)
+
+BEGIN;
+SELECT bt_index_check('bttest_a_idx');
+ bt_index_check
+----------------
+
+(1 row)
+
+SELECT bt_index_parent_check('bttest_b_idx');
+ bt_index_parent_check
+-----------------------
+
+(1 row)
+
+-- 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();
+ locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
+----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------+---------+----------
+(0 rows)
+
+COMMIT;
+-- Deduplication
+TRUNCATE bttest_a;
+INSERT INTO bttest_a SELECT 42 FROM generate_series(1, 2000);
+SELECT bt_index_check('bttest_a_idx', true);
+ bt_index_check
+----------------
+
+(1 row)
+
+-- normal check outside of xact for index with included columns
+SELECT bt_index_check('bttest_multi_idx');
+ bt_index_check
+----------------
+
+(1 row)
+
+-- more expansive tests for index with included columns
+SELECT bt_index_parent_check('bttest_multi_idx', true, true);
+ bt_index_parent_check
+-----------------------
+
+(1 row)
+
+-- 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);
+ bt_index_parent_check
+-----------------------
+
+(1 row)
+
+--
+-- 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);
+ bt_index_parent_check
+-----------------------
+
+(1 row)
+
+--
+-- 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';
+ attstorage
+------------
+ x
+(1 row)
+
+-- 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);
+ bt_index_check
+----------------
+
+(1 row)
+
+-- cleanup
+DROP TABLE bttest_a;
+DROP TABLE bttest_b;
+DROP TABLE bttest_multi;
+DROP TABLE delete_test_table;
+DROP TABLE toast_bug;
+DROP OWNED BY regress_bttest_role; -- permissions
+DROP ROLE regress_bttest_role;