src/test/regress/sql/create_am.sql

   1 --
   2 -- Create access method tests
   3 --
   4
   5 -- Make gist2 over gisthandler. In fact, it would be a synonym to gist.
   6 CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;
   7
   8 -- Verify return type checks for handlers
   9 CREATE ACCESS METHOD bogus TYPE INDEX HANDLER int4in;
  10 CREATE 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
  14 CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
  15
  16 -- Make operator class for boxes using gist2
  17 CREATE 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         FUNCTION 1      gist_box_consistent(internal, box, smallint, oid, internal),
  32         FUNCTION 2      gist_box_union(internal, internal),
  33         -- don't need compress, decompress, or fetch functions
  34         FUNCTION 5      gist_box_penalty(internal, internal, internal),
  35         FUNCTION 6      gist_box_picksplit(internal, internal),
  36         FUNCTION 7      gist_box_same(box, box, internal);
  37
  38 -- Create gist2 index on fast_emp4000
  39 CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
  40
  41 -- Now check the results from plain indexscan; temporarily drop existing
  42 -- index grect2ind to ensure it doesn't capture the plan
  43 BEGIN;
  44 DROP INDEX grect2ind;
  45 SET enable_seqscan = OFF;
  46 SET enable_indexscan = ON;
  47 SET enable_bitmapscan = OFF;
  48
  49 EXPLAIN (COSTS OFF)
  50 SELECT * FROM fast_emp4000
  51     WHERE home_base <@ '(200,200),(2000,1000)'::box
  52     ORDER BY (home_base[0])[0];
  53 SELECT * FROM fast_emp4000
  54     WHERE home_base <@ '(200,200),(2000,1000)'::box
  55     ORDER BY (home_base[0])[0];
  56
  57 EXPLAIN (COSTS OFF)
  58 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  59 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  60
  61 EXPLAIN (COSTS OFF)
  62 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
  63 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
  64
  65 ROLLBACK;
  66
  67 -- Try to drop access method: fail because of dependent objects
  68 DROP ACCESS METHOD gist2;
  69
  70 -- Drop access method cascade
  71 -- To prevent a (rare) deadlock against autovacuum,
  72 -- we must lock the table that owns the index that will be dropped
  73 BEGIN;
  74 LOCK TABLE fast_emp4000;
  75 DROP ACCESS METHOD gist2 CASCADE;
  76 COMMIT;
  77
  78
  79 --
  80 -- Test table access methods
  81 --
  82
  83 -- prevent empty values
  84 SET default_table_access_method = '';
  85
  86 -- prevent nonexistent values
  87 SET default_table_access_method = 'I do not exist AM';
  88
  89 -- prevent setting it to an index AM
  90 SET default_table_access_method = 'btree';
  91
  92
  93 -- Create a heap2 table am handler with heapam handler
  94 CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;
  95
  96 -- Verify return type checks for handlers
  97 CREATE ACCESS METHOD bogus TYPE TABLE HANDLER int4in;
  98 CREATE ACCESS METHOD bogus TYPE TABLE HANDLER bthandler;
  99
 100 SELECT amname, amhandler, amtype FROM pg_am where amtype = 't' ORDER BY 1, 2;
 101
 102
 103 -- First create tables employing the new AM using USING
 104
 105 -- plain CREATE TABLE
 106 CREATE TABLE tableam_tbl_heap2(f1 int) USING heap2;
 107 INSERT INTO tableam_tbl_heap2 VALUES(1);
 108 SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1;
 109
 110 -- CREATE TABLE AS
 111 CREATE TABLE tableam_tblas_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
 112 SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1;
 113
 114 -- SELECT INTO doesn't support USING
 115 SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2;
 116
 117 -- CREATE VIEW doesn't support USING
 118 CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
 119
 120 -- CREATE SEQUENCE doesn't support USING
 121 CREATE SEQUENCE tableam_seq_heap2 USING heap2;
 122
 123 -- CREATE MATERIALIZED VIEW does support USING
 124 CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
 125 SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
 126
 127 -- CREATE TABLE ..  PARTITION BY supports USING.
 128 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
 129 SELECT a.amname FROM pg_class c, pg_am a
 130   WHERE c.relname = 'tableam_parted_heap2' AND a.oid = c.relam;
 131 DROP TABLE tableam_parted_heap2;
 132
 133 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
 134 -- new partitions will inherit from the current default, rather the partition root
 135 SET default_table_access_method = 'heap';
 136 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
 137 SET default_table_access_method = 'heap2';
 138 CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b');
 139 RESET default_table_access_method;
 140 -- but the method can be explicitly specified
 141 CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap;
 142 CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2;
 143
 144 -- List all objects in AM
 145 SELECT
 146     pc.relkind,
 147     pa.amname,
 148     CASE WHEN relkind = 't' THEN
 149         (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid)
 150     ELSE
 151         relname::regclass::text
 152     END COLLATE "C" AS relname
 153 FROM pg_class AS pc,
 154     pg_am AS pa
 155 WHERE pa.oid = pc.relam
 156    AND pa.amname = 'heap2'
 157 ORDER BY 3, 1, 2;
 158
 159 -- Show dependencies onto AM - there shouldn't be any for toast
 160 SELECT pg_describe_object(classid,objid,objsubid) AS obj
 161 FROM pg_depend, pg_am
 162 WHERE pg_depend.refclassid = 'pg_am'::regclass
 163     AND pg_am.oid = pg_depend.refobjid
 164     AND pg_am.amname = 'heap2'
 165 ORDER BY classid, objid, objsubid;
 166
 167 -- ALTER TABLE SET ACCESS METHOD
 168 CREATE TABLE heaptable USING heap AS
 169   SELECT a, repeat(a::text, 100) FROM generate_series(1,9) AS a;
 170 SELECT amname FROM pg_class c, pg_am am
 171   WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 172 -- Switching to heap2 adds new dependency entry to the AM.
 173 ALTER TABLE heaptable SET ACCESS METHOD heap2;
 174 SELECT pg_describe_object(classid, objid, objsubid) as obj,
 175        pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
 176        deptype
 177   FROM pg_depend
 178   WHERE classid = 'pg_class'::regclass AND
 179         objid = 'heaptable'::regclass
 180   ORDER BY 1, 2;
 181 -- Switching to heap should not have a dependency entry to the AM.
 182 ALTER TABLE heaptable SET ACCESS METHOD heap;
 183 SELECT pg_describe_object(classid, objid, objsubid) as obj,
 184        pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
 185        deptype
 186   FROM pg_depend
 187   WHERE classid = 'pg_class'::regclass AND
 188         objid = 'heaptable'::regclass
 189   ORDER BY 1, 2;
 190 ALTER TABLE heaptable SET ACCESS METHOD heap2;
 191 SELECT amname FROM pg_class c, pg_am am
 192   WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 193 SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
 194 -- DEFAULT access method
 195 BEGIN;
 196 SET LOCAL default_table_access_method TO heap2;
 197 ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
 198 SELECT amname FROM pg_class c, pg_am am
 199   WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 200 SET LOCAL default_table_access_method TO heap;
 201 ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
 202 SELECT amname FROM pg_class c, pg_am am
 203   WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 204 ROLLBACK;
 205 -- ALTER MATERIALIZED VIEW SET ACCESS METHOD
 206 CREATE MATERIALIZED VIEW heapmv USING heap AS SELECT * FROM heaptable;
 207 SELECT amname FROM pg_class c, pg_am am
 208   WHERE c.relam = am.oid AND c.oid = 'heapmv'::regclass;
 209 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap2;
 210 SELECT amname FROM pg_class c, pg_am am
 211   WHERE c.relam = am.oid AND c.oid = 'heapmv'::regclass;
 212 SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heapmv;
 213 -- No support for multiple subcommands
 214 ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 215 ALTER TABLE heaptable SET ACCESS METHOD DEFAULT, SET ACCESS METHOD heap2;
 216 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 217 DROP MATERIALIZED VIEW heapmv;
 218 DROP TABLE heaptable;
 219
 220 -- Partition hierarchies with access methods
 221 BEGIN;
 222 SET LOCAL default_table_access_method = 'heap';
 223 CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
 224 -- pg_class.relam is 0, no dependency recorded between the AM and the
 225 -- partitioned table.
 226 SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
 227 SELECT pg_describe_object(classid, objid, objsubid) AS obj,
 228        pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
 229   FROM pg_depend, pg_am
 230   WHERE pg_depend.refclassid = 'pg_am'::regclass
 231     AND pg_am.oid = pg_depend.refobjid
 232     AND pg_depend.objid = 'am_partitioned'::regclass;
 233 -- New default is set, with dependency added.
 234 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
 235 SELECT a.amname FROM pg_class c, pg_am a
 236   WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
 237 SELECT pg_describe_object(classid, objid, objsubid) AS obj,
 238        pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
 239   FROM pg_depend, pg_am
 240   WHERE pg_depend.refclassid = 'pg_am'::regclass
 241     AND pg_am.oid = pg_depend.refobjid
 242     AND pg_depend.objid = 'am_partitioned'::regclass;
 243 -- Default is set, with dependency updated.
 244 SET LOCAL default_table_access_method = 'heap2';
 245 ALTER TABLE am_partitioned SET ACCESS METHOD heap;
 246 SELECT a.amname FROM pg_class c, pg_am a
 247   WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
 248 -- Dependency pinned, hence removed.
 249 SELECT pg_describe_object(classid, objid, objsubid) AS obj,
 250        pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
 251   FROM pg_depend, pg_am
 252   WHERE pg_depend.refclassid = 'pg_am'::regclass
 253     AND pg_am.oid = pg_depend.refobjid
 254     AND pg_depend.objid = 'am_partitioned'::regclass;
 255 -- Default and AM set in the clause are the same, relam should be set.
 256 SET LOCAL default_table_access_method = 'heap2';
 257 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
 258 SELECT a.amname FROM pg_class c, pg_am a
 259   WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
 260 -- Reset to default
 261 ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
 262 SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
 263 -- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
 264 -- will inherit the AM set.  Existing partitioned are unchanged.
 265 SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
 266 SET LOCAL default_table_access_method = 'heap';
 267 CREATE TABLE am_partitioned_0 PARTITION OF am_partitioned
 268   FOR VALUES WITH (MODULUS 10, REMAINDER 0);
 269 SET LOCAL default_table_access_method = 'heap2';
 270 CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned
 271   FOR VALUES WITH (MODULUS 10, REMAINDER 1);
 272 SET LOCAL default_table_access_method = 'heap';
 273 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
 274 CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned
 275   FOR VALUES WITH (MODULUS 10, REMAINDER 2);
 276 ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
 277 SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
 278 CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned
 279   FOR VALUES WITH (MODULUS 10, REMAINDER 3);
 280 -- Partitioned table with relam at 0
 281 ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
 282 CREATE TABLE am_partitioned_5p PARTITION OF am_partitioned
 283   FOR VALUES WITH (MODULUS 10, REMAINDER 5) PARTITION BY hash(y);
 284 -- Partitions of this partitioned table inherit default AM at creation
 285 -- time.
 286 CREATE TABLE am_partitioned_5p1 PARTITION OF am_partitioned_5p
 287   FOR VALUES WITH (MODULUS 10, REMAINDER 1);
 288 -- Partitioned table with relam set.
 289 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
 290 CREATE TABLE am_partitioned_6p PARTITION OF am_partitioned
 291   FOR VALUES WITH (MODULUS 10, REMAINDER 6) PARTITION BY hash(y);
 292 -- Partitions of this partitioned table inherit its AM.
 293 CREATE TABLE am_partitioned_6p1 PARTITION OF am_partitioned_6p
 294   FOR VALUES WITH (MODULUS 10, REMAINDER 1);
 295 SELECT c.relname, a.amname FROM pg_class c, pg_am a
 296   WHERE c.relam = a.oid AND
 297         c.relname LIKE 'am_partitioned%'
 298 UNION ALL
 299 SELECT c.relname, 'default' FROM pg_class c
 300   WHERE c.relam = 0
 301         AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
 302 DROP TABLE am_partitioned;
 303 COMMIT;
 304
 305 -- Second, create objects in the new AM by changing the default AM
 306 BEGIN;
 307 SET LOCAL default_table_access_method = 'heap2';
 308
 309 -- following tests should all respect the default AM
 310 CREATE TABLE tableam_tbl_heapx(f1 int);
 311 CREATE TABLE tableam_tblas_heapx AS SELECT * FROM tableam_tbl_heapx;
 312 SELECT INTO tableam_tblselectinto_heapx FROM tableam_tbl_heapx;
 313 CREATE MATERIALIZED VIEW tableam_tblmv_heapx USING heap2 AS SELECT * FROM tableam_tbl_heapx;
 314 CREATE TABLE tableam_parted_heapx (a text, b int) PARTITION BY list (a);
 315 CREATE TABLE tableam_parted_1_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('a', 'b');
 316
 317 -- but an explicitly set AM overrides it
 318 CREATE TABLE tableam_parted_2_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('c', 'd') USING heap;
 319
 320 -- sequences, views and foreign servers shouldn't have an AM
 321 CREATE VIEW tableam_view_heapx AS SELECT * FROM tableam_tbl_heapx;
 322 CREATE SEQUENCE tableam_seq_heapx;
 323 CREATE FOREIGN DATA WRAPPER fdw_heap2 VALIDATOR postgresql_fdw_validator;
 324 CREATE SERVER fs_heap2 FOREIGN DATA WRAPPER fdw_heap2 ;
 325 CREATE FOREIGN table tableam_fdw_heapx () SERVER fs_heap2;
 326
 327 -- Verify that new AM was used for tables, matviews, but not for sequences, views and fdws
 328 SELECT
 329     pc.relkind,
 330     pa.amname,
 331     CASE WHEN relkind = 't' THEN
 332         (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid)
 333     ELSE
 334         relname::regclass::text
 335     END COLLATE "C" AS relname
 336 FROM pg_class AS pc
 337     LEFT JOIN pg_am AS pa ON (pa.oid = pc.relam)
 338 WHERE pc.relname LIKE 'tableam_%_heapx'
 339 ORDER BY 3, 1, 2;
 340
 341 -- don't want to keep those tables, nor the default
 342 ROLLBACK;
 343
 344 -- Third, check that we can neither create a table using a nonexistent
 345 -- AM, nor using an index AM
 346 CREATE TABLE i_am_a_failure() USING "";
 347 CREATE TABLE i_am_a_failure() USING i_do_not_exist_am;
 348 CREATE TABLE i_am_a_failure() USING "I do not exist AM";
 349 CREATE TABLE i_am_a_failure() USING "btree";
 350
 351 -- Other weird invalid cases that cause problems
 352 CREATE FOREIGN TABLE fp PARTITION OF tableam_parted_a_heap2 DEFAULT SERVER x;
 353
 354 -- Drop table access method, which fails as objects depends on it
 355 DROP ACCESS METHOD heap2;
 356
 357 -- we intentionally leave the objects created above alive, to verify pg_dump support