1-- 2-- Create access method tests 3-- 4 5-- Make gist2 over gisthandler. In fact, it would be a synonym to gist. 6CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler; 7 8-- Verify return type checks for handlers 9CREATE ACCESS METHOD bogus TYPE INDEX HANDLER int4in; 10CREATE ACCESS METHOD bogus TYPE INDEX HANDLER heap_tableam_handler; 11 12 13-- Try to create gist2 index on fast_emp4000: fail because opclass doesn't exist 14CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base); 15 16-- Make operator class for boxes using gist2 17CREATE OPERATOR CLASS box_ops DEFAULT 18 FOR TYPE box USING gist2 AS 19 OPERATOR 1 <<, 20 OPERATOR 2 &<, 21 OPERATOR 3 &&, 22 OPERATOR 4 &>, 23 OPERATOR 5 >>, 24 OPERATOR 6 ~=, 25 OPERATOR 7 @>, 26 OPERATOR 8 <@, 27 OPERATOR 9 &<|, 28 OPERATOR 10 <<|, 29 OPERATOR 11 |>>, 30 OPERATOR 12 |&>, 31 OPERATOR 13 ~, 32 OPERATOR 14 @, 33 FUNCTION 1 gist_box_consistent(internal, box, smallint, oid, internal), 34 FUNCTION 2 gist_box_union(internal, internal), 35 -- don't need compress, decompress, or fetch functions 36 FUNCTION 5 gist_box_penalty(internal, internal, internal), 37 FUNCTION 6 gist_box_picksplit(internal, internal), 38 FUNCTION 7 gist_box_same(box, box, internal); 39 40-- Create gist2 index on fast_emp4000 41CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base); 42 43-- Now check the results from plain indexscan; temporarily drop existing 44-- index grect2ind to ensure it doesn't capture the plan 45BEGIN; 46DROP INDEX grect2ind; 47SET enable_seqscan = OFF; 48SET enable_indexscan = ON; 49SET enable_bitmapscan = OFF; 50 51EXPLAIN (COSTS OFF) 52SELECT * FROM fast_emp4000 53 WHERE home_base @ '(200,200),(2000,1000)'::box 54 ORDER BY (home_base[0])[0]; 55SELECT * FROM fast_emp4000 56 WHERE home_base @ '(200,200),(2000,1000)'::box 57 ORDER BY (home_base[0])[0]; 58 59EXPLAIN (COSTS OFF) 60SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 61SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 62 63EXPLAIN (COSTS OFF) 64SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 65SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 66 67ROLLBACK; 68 69-- Try to drop access method: fail because of dependent objects 70DROP ACCESS METHOD gist2; 71 72-- Drop access method cascade 73-- To prevent a (rare) deadlock against autovacuum, 74-- we must lock the table that owns the index that will be dropped 75BEGIN; 76LOCK TABLE fast_emp4000; 77DROP ACCESS METHOD gist2 CASCADE; 78COMMIT; 79 80 81-- 82-- Test table access methods 83-- 84 85-- prevent empty values 86SET default_table_access_method = ''; 87 88-- prevent nonexistent values 89SET default_table_access_method = 'I do not exist AM'; 90 91-- prevent setting it to an index AM 92SET default_table_access_method = 'btree'; 93 94 95-- Create a heap2 table am handler with heapam handler 96CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler; 97 98-- Verify return type checks for handlers 99CREATE ACCESS METHOD bogus TYPE TABLE HANDLER int4in; 100CREATE ACCESS METHOD bogus TYPE TABLE HANDLER bthandler; 101 102SELECT amname, amhandler, amtype FROM pg_am where amtype = 't' ORDER BY 1, 2; 103 104 105-- First create tables employing the new AM using USING 106 107-- plain CREATE TABLE 108CREATE TABLE tableam_tbl_heap2(f1 int) USING heap2; 109INSERT INTO tableam_tbl_heap2 VALUES(1); 110SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1; 111 112-- CREATE TABLE AS 113CREATE TABLE tableam_tblas_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; 114SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1; 115 116-- SELECT INTO doesn't support USING 117SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2; 118 119-- CREATE VIEW doesn't support USING 120CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; 121 122-- CREATE SEQUENCE doesn't support USING 123CREATE SEQUENCE tableam_seq_heap2 USING heap2; 124 125-- CREATE MATERIALIZED VIEW does support USING 126CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; 127SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1; 128 129-- CREATE TABLE .. PARTITION BY doesn't not support USING 130CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2; 131 132CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a); 133-- new partitions will inherit from the current default, rather the partition root 134SET default_table_access_method = 'heap'; 135CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a'); 136SET default_table_access_method = 'heap2'; 137CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b'); 138RESET default_table_access_method; 139-- but the method can be explicitly specified 140CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap; 141CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2; 142 143-- List all objects in AM 144SELECT 145 pc.relkind, 146 pa.amname, 147 CASE WHEN relkind = 't' THEN 148 (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid) 149 ELSE 150 relname::regclass::text 151 END COLLATE "C" AS relname 152FROM pg_class AS pc, 153 pg_am AS pa 154WHERE pa.oid = pc.relam 155 AND pa.amname = 'heap2' 156ORDER BY 3, 1, 2; 157 158-- Show dependencies onto AM - there shouldn't be any for toast 159SELECT pg_describe_object(classid,objid,objsubid) AS obj 160FROM pg_depend, pg_am 161WHERE pg_depend.refclassid = 'pg_am'::regclass 162 AND pg_am.oid = pg_depend.refobjid 163 AND pg_am.amname = 'heap2' 164ORDER BY classid, objid, objsubid; 165 166 167-- Second, create objects in the new AM by changing the default AM 168BEGIN; 169SET LOCAL default_table_access_method = 'heap2'; 170 171-- following tests should all respect the default AM 172CREATE TABLE tableam_tbl_heapx(f1 int); 173CREATE TABLE tableam_tblas_heapx AS SELECT * FROM tableam_tbl_heapx; 174SELECT INTO tableam_tblselectinto_heapx FROM tableam_tbl_heapx; 175CREATE MATERIALIZED VIEW tableam_tblmv_heapx USING heap2 AS SELECT * FROM tableam_tbl_heapx; 176CREATE TABLE tableam_parted_heapx (a text, b int) PARTITION BY list (a); 177CREATE TABLE tableam_parted_1_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('a', 'b'); 178 179-- but an explicitly set AM overrides it 180CREATE TABLE tableam_parted_2_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('c', 'd') USING heap; 181 182-- sequences, views and foreign servers shouldn't have an AM 183CREATE VIEW tableam_view_heapx AS SELECT * FROM tableam_tbl_heapx; 184CREATE SEQUENCE tableam_seq_heapx; 185CREATE FOREIGN DATA WRAPPER fdw_heap2 VALIDATOR postgresql_fdw_validator; 186CREATE SERVER fs_heap2 FOREIGN DATA WRAPPER fdw_heap2 ; 187CREATE FOREIGN table tableam_fdw_heapx () SERVER fs_heap2; 188 189-- Verify that new AM was used for tables, matviews, but not for sequences, views and fdws 190SELECT 191 pc.relkind, 192 pa.amname, 193 CASE WHEN relkind = 't' THEN 194 (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid) 195 ELSE 196 relname::regclass::text 197 END COLLATE "C" AS relname 198FROM pg_class AS pc 199 LEFT JOIN pg_am AS pa ON (pa.oid = pc.relam) 200WHERE pc.relname LIKE 'tableam_%_heapx' 201ORDER BY 3, 1, 2; 202 203-- don't want to keep those tables, nor the default 204ROLLBACK; 205 206-- Third, check that we can neither create a table using a nonexistent 207-- AM, nor using an index AM 208CREATE TABLE i_am_a_failure() USING ""; 209CREATE TABLE i_am_a_failure() USING i_do_not_exist_am; 210CREATE TABLE i_am_a_failure() USING "I do not exist AM"; 211CREATE TABLE i_am_a_failure() USING "btree"; 212 213-- Drop table access method, which fails as objects depends on it 214DROP ACCESS METHOD heap2; 215 216-- we intentionally leave the objects created above alive, to verify pg_dump support 217