2 -- Create access method tests
4 -- Make gist2 over gisthandler. In fact, it would be a synonym to gist.
5 CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;
6 -- Verify return type checks for handlers
7 CREATE ACCESS METHOD bogus TYPE INDEX HANDLER int4in;
8 ERROR: function int4in(internal) does not exist
9 CREATE ACCESS METHOD bogus TYPE INDEX HANDLER heap_tableam_handler;
10 ERROR: function heap_tableam_handler must return type index_am_handler
11 -- Try to create gist2 index on fast_emp4000: fail because opclass doesn't exist
12 CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
13 ERROR: data type box has no default operator class for access method "gist2"
14 HINT: You must specify an operator class for the index or define a default operator class for the data type.
15 -- Make operator class for boxes using gist2
16 CREATE OPERATOR CLASS box_ops DEFAULT
17 FOR TYPE box USING gist2 AS
30 FUNCTION 1 gist_box_consistent(internal, box, smallint, oid, internal),
31 FUNCTION 2 gist_box_union(internal, internal),
32 -- don't need compress, decompress, or fetch functions
33 FUNCTION 5 gist_box_penalty(internal, internal, internal),
34 FUNCTION 6 gist_box_picksplit(internal, internal),
35 FUNCTION 7 gist_box_same(box, box, internal);
36 -- Create gist2 index on fast_emp4000
37 CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
38 -- Now check the results from plain indexscan; temporarily drop existing
39 -- index grect2ind to ensure it doesn't capture the plan
42 SET enable_seqscan = OFF;
43 SET enable_indexscan = ON;
44 SET enable_bitmapscan = OFF;
46 SELECT * FROM fast_emp4000
47 WHERE home_base <@ '(200,200),(2000,1000)'::box
48 ORDER BY (home_base[0])[0];
50 -----------------------------------------------------------------
52 Sort Key: ((home_base[0])[0])
53 -> Index Only Scan using grect2ind2 on fast_emp4000
54 Index Cond: (home_base <@ '(2000,1000),(200,200)'::box)
57 SELECT * FROM fast_emp4000
58 WHERE home_base <@ '(200,200),(2000,1000)'::box
59 ORDER BY (home_base[0])[0];
61 -----------------------
67 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
69 -------------------------------------------------------------
71 -> Index Only Scan using grect2ind2 on fast_emp4000
72 Index Cond: (home_base && '(1000,1000),(0,0)'::box)
75 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
82 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
84 --------------------------------------------------------
86 -> Index Only Scan using grect2ind2 on fast_emp4000
87 Index Cond: (home_base IS NULL)
90 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
97 -- Try to drop access method: fail because of dependent objects
98 DROP ACCESS METHOD gist2;
99 ERROR: cannot drop access method gist2 because other objects depend on it
100 DETAIL: index grect2ind2 depends on operator class box_ops for access method gist2
101 HINT: Use DROP ... CASCADE to drop the dependent objects too.
102 -- Drop access method cascade
103 -- To prevent a (rare) deadlock against autovacuum,
104 -- we must lock the table that owns the index that will be dropped
106 LOCK TABLE fast_emp4000;
107 DROP ACCESS METHOD gist2 CASCADE;
108 NOTICE: drop cascades to index grect2ind2
111 -- Test table access methods
113 -- prevent empty values
114 SET default_table_access_method = '';
115 ERROR: invalid value for parameter "default_table_access_method": ""
116 DETAIL: default_table_access_method cannot be empty.
117 -- prevent nonexistent values
118 SET default_table_access_method = 'I do not exist AM';
119 ERROR: invalid value for parameter "default_table_access_method": "I do not exist AM"
120 DETAIL: Table access method "I do not exist AM" does not exist.
121 -- prevent setting it to an index AM
122 SET default_table_access_method = 'btree';
123 ERROR: access method "btree" is not of type TABLE
124 -- Create a heap2 table am handler with heapam handler
125 CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;
126 -- Verify return type checks for handlers
127 CREATE ACCESS METHOD bogus TYPE TABLE HANDLER int4in;
128 ERROR: function int4in(internal) does not exist
129 CREATE ACCESS METHOD bogus TYPE TABLE HANDLER bthandler;
130 ERROR: function bthandler must return type table_am_handler
131 SELECT amname, amhandler, amtype FROM pg_am where amtype = 't' ORDER BY 1, 2;
132 amname | amhandler | amtype
133 --------+----------------------+--------
134 heap | heap_tableam_handler | t
135 heap2 | heap_tableam_handler | t
138 -- First create tables employing the new AM using USING
139 -- plain CREATE TABLE
140 CREATE TABLE tableam_tbl_heap2(f1 int) USING heap2;
141 INSERT INTO tableam_tbl_heap2 VALUES(1);
142 SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1;
149 CREATE TABLE tableam_tblas_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
150 SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1;
156 -- SELECT INTO doesn't support USING
157 SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2;
158 ERROR: syntax error at or near "USING"
159 LINE 1: SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tab...
161 -- CREATE VIEW doesn't support USING
162 CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
163 ERROR: syntax error at or near "USING"
164 LINE 1: CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM ...
166 -- CREATE SEQUENCE doesn't support USING
167 CREATE SEQUENCE tableam_seq_heap2 USING heap2;
168 ERROR: syntax error at or near "USING"
169 LINE 1: CREATE SEQUENCE tableam_seq_heap2 USING heap2;
171 -- CREATE MATERIALIZED VIEW does support USING
172 CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
173 SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
179 -- CREATE TABLE .. PARTITION BY supports USING.
180 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
181 SELECT a.amname FROM pg_class c, pg_am a
182 WHERE c.relname = 'tableam_parted_heap2' AND a.oid = c.relam;
188 DROP TABLE tableam_parted_heap2;
189 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
190 -- new partitions will inherit from the current default, rather the partition root
191 SET default_table_access_method = 'heap';
192 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
193 SET default_table_access_method = 'heap2';
194 CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b');
195 RESET default_table_access_method;
196 -- but the method can be explicitly specified
197 CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap;
198 CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2;
199 -- List all objects in AM
203 CASE WHEN relkind = 't' THEN
204 (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid)
206 relname::regclass::text
207 END COLLATE "C" AS relname
210 WHERE pa.oid = pc.relam
211 AND pa.amname = 'heap2'
213 relkind | amname | relname
214 ---------+--------+----------------------------------
215 r | heap2 | tableam_parted_b_heap2
216 r | heap2 | tableam_parted_d_heap2
217 r | heap2 | tableam_tbl_heap2
218 r | heap2 | tableam_tblas_heap2
219 m | heap2 | tableam_tblmv_heap2
220 t | heap2 | toast for tableam_parted_b_heap2
221 t | heap2 | toast for tableam_parted_d_heap2
224 -- Show dependencies onto AM - there shouldn't be any for toast
225 SELECT pg_describe_object(classid,objid,objsubid) AS obj
226 FROM pg_depend, pg_am
227 WHERE pg_depend.refclassid = 'pg_am'::regclass
228 AND pg_am.oid = pg_depend.refobjid
229 AND pg_am.amname = 'heap2'
230 ORDER BY classid, objid, objsubid;
232 ---------------------------------------
233 table tableam_tbl_heap2
234 table tableam_tblas_heap2
235 materialized view tableam_tblmv_heap2
236 table tableam_parted_b_heap2
237 table tableam_parted_d_heap2
240 -- ALTER TABLE SET ACCESS METHOD
241 CREATE TABLE heaptable USING heap AS
242 SELECT a, repeat(a::text, 100) FROM generate_series(1,9) AS a;
243 SELECT amname FROM pg_class c, pg_am am
244 WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
250 -- Switching to heap2 adds new dependency entry to the AM.
251 ALTER TABLE heaptable SET ACCESS METHOD heap2;
252 SELECT pg_describe_object(classid, objid, objsubid) as obj,
253 pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
256 WHERE classid = 'pg_class'::regclass AND
257 objid = 'heaptable'::regclass
259 obj | objref | deptype
260 -----------------+---------------------+---------
261 table heaptable | access method heap2 | n
262 table heaptable | schema public | n
265 -- Switching to heap should not have a dependency entry to the AM.
266 ALTER TABLE heaptable SET ACCESS METHOD heap;
267 SELECT pg_describe_object(classid, objid, objsubid) as obj,
268 pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
271 WHERE classid = 'pg_class'::regclass AND
272 objid = 'heaptable'::regclass
274 obj | objref | deptype
275 -----------------+---------------+---------
276 table heaptable | schema public | n
279 ALTER TABLE heaptable SET ACCESS METHOD heap2;
280 SELECT amname FROM pg_class c, pg_am am
281 WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
287 SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
293 -- DEFAULT access method
295 SET LOCAL default_table_access_method TO heap2;
296 ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
297 SELECT amname FROM pg_class c, pg_am am
298 WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
304 SET LOCAL default_table_access_method TO heap;
305 ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
306 SELECT amname FROM pg_class c, pg_am am
307 WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
314 -- ALTER MATERIALIZED VIEW SET ACCESS METHOD
315 CREATE MATERIALIZED VIEW heapmv USING heap AS SELECT * FROM heaptable;
316 SELECT amname FROM pg_class c, pg_am am
317 WHERE c.relam = am.oid AND c.oid = 'heapmv'::regclass;
323 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap2;
324 SELECT amname FROM pg_class c, pg_am am
325 WHERE c.relam = am.oid AND c.oid = 'heapmv'::regclass;
331 SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heapmv;
337 -- No support for multiple subcommands
338 ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
339 ERROR: cannot have multiple SET ACCESS METHOD subcommands
340 ALTER TABLE heaptable SET ACCESS METHOD DEFAULT, SET ACCESS METHOD heap2;
341 ERROR: cannot have multiple SET ACCESS METHOD subcommands
342 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
343 ERROR: cannot have multiple SET ACCESS METHOD subcommands
344 DROP MATERIALIZED VIEW heapmv;
345 DROP TABLE heaptable;
346 -- Partition hierarchies with access methods
348 SET LOCAL default_table_access_method = 'heap';
349 CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
350 -- pg_class.relam is 0, no dependency recorded between the AM and the
351 -- partitioned table.
352 SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
358 SELECT pg_describe_object(classid, objid, objsubid) AS obj,
359 pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
360 FROM pg_depend, pg_am
361 WHERE pg_depend.refclassid = 'pg_am'::regclass
362 AND pg_am.oid = pg_depend.refobjid
363 AND pg_depend.objid = 'am_partitioned'::regclass;
368 -- New default is set, with dependency added.
369 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
370 SELECT a.amname FROM pg_class c, pg_am a
371 WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
377 SELECT pg_describe_object(classid, objid, objsubid) AS obj,
378 pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
379 FROM pg_depend, pg_am
380 WHERE pg_depend.refclassid = 'pg_am'::regclass
381 AND pg_am.oid = pg_depend.refobjid
382 AND pg_depend.objid = 'am_partitioned'::regclass;
384 ----------------------+---------------------
385 table am_partitioned | access method heap2
388 -- Default is set, with dependency updated.
389 SET LOCAL default_table_access_method = 'heap2';
390 ALTER TABLE am_partitioned SET ACCESS METHOD heap;
391 SELECT a.amname FROM pg_class c, pg_am a
392 WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
398 -- Dependency pinned, hence removed.
399 SELECT pg_describe_object(classid, objid, objsubid) AS obj,
400 pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
401 FROM pg_depend, pg_am
402 WHERE pg_depend.refclassid = 'pg_am'::regclass
403 AND pg_am.oid = pg_depend.refobjid
404 AND pg_depend.objid = 'am_partitioned'::regclass;
409 -- Default and AM set in the clause are the same, relam should be set.
410 SET LOCAL default_table_access_method = 'heap2';
411 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
412 SELECT a.amname FROM pg_class c, pg_am a
413 WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
420 ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
421 SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
427 -- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
428 -- will inherit the AM set. Existing partitioned are unchanged.
429 SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
435 SET LOCAL default_table_access_method = 'heap';
436 CREATE TABLE am_partitioned_0 PARTITION OF am_partitioned
437 FOR VALUES WITH (MODULUS 10, REMAINDER 0);
438 SET LOCAL default_table_access_method = 'heap2';
439 CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned
440 FOR VALUES WITH (MODULUS 10, REMAINDER 1);
441 SET LOCAL default_table_access_method = 'heap';
442 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
443 CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned
444 FOR VALUES WITH (MODULUS 10, REMAINDER 2);
445 ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
446 SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
452 CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned
453 FOR VALUES WITH (MODULUS 10, REMAINDER 3);
454 -- Partitioned table with relam at 0
455 ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
456 CREATE TABLE am_partitioned_5p PARTITION OF am_partitioned
457 FOR VALUES WITH (MODULUS 10, REMAINDER 5) PARTITION BY hash(y);
458 -- Partitions of this partitioned table inherit default AM at creation
460 CREATE TABLE am_partitioned_5p1 PARTITION OF am_partitioned_5p
461 FOR VALUES WITH (MODULUS 10, REMAINDER 1);
462 -- Partitioned table with relam set.
463 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
464 CREATE TABLE am_partitioned_6p PARTITION OF am_partitioned
465 FOR VALUES WITH (MODULUS 10, REMAINDER 6) PARTITION BY hash(y);
466 -- Partitions of this partitioned table inherit its AM.
467 CREATE TABLE am_partitioned_6p1 PARTITION OF am_partitioned_6p
468 FOR VALUES WITH (MODULUS 10, REMAINDER 1);
469 SELECT c.relname, a.amname FROM pg_class c, pg_am a
470 WHERE c.relam = a.oid AND
471 c.relname LIKE 'am_partitioned%'
473 SELECT c.relname, 'default' FROM pg_class c
475 AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
477 --------------------+---------
478 am_partitioned | heap2
479 am_partitioned_0 | heap
480 am_partitioned_1 | heap2
481 am_partitioned_2 | heap2
482 am_partitioned_3 | heap
483 am_partitioned_5p | default
484 am_partitioned_5p1 | heap
485 am_partitioned_6p | heap2
486 am_partitioned_6p1 | heap2
489 DROP TABLE am_partitioned;
491 -- Second, create objects in the new AM by changing the default AM
493 SET LOCAL default_table_access_method = 'heap2';
494 -- following tests should all respect the default AM
495 CREATE TABLE tableam_tbl_heapx(f1 int);
496 CREATE TABLE tableam_tblas_heapx AS SELECT * FROM tableam_tbl_heapx;
497 SELECT INTO tableam_tblselectinto_heapx FROM tableam_tbl_heapx;
498 CREATE MATERIALIZED VIEW tableam_tblmv_heapx USING heap2 AS SELECT * FROM tableam_tbl_heapx;
499 CREATE TABLE tableam_parted_heapx (a text, b int) PARTITION BY list (a);
500 CREATE TABLE tableam_parted_1_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('a', 'b');
501 -- but an explicitly set AM overrides it
502 CREATE TABLE tableam_parted_2_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('c', 'd') USING heap;
503 -- sequences, views and foreign servers shouldn't have an AM
504 CREATE VIEW tableam_view_heapx AS SELECT * FROM tableam_tbl_heapx;
505 CREATE SEQUENCE tableam_seq_heapx;
506 CREATE FOREIGN DATA WRAPPER fdw_heap2 VALIDATOR postgresql_fdw_validator;
507 CREATE SERVER fs_heap2 FOREIGN DATA WRAPPER fdw_heap2 ;
508 CREATE FOREIGN table tableam_fdw_heapx () SERVER fs_heap2;
509 -- Verify that new AM was used for tables, matviews, but not for sequences, views and fdws
513 CASE WHEN relkind = 't' THEN
514 (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid)
516 relname::regclass::text
517 END COLLATE "C" AS relname
519 LEFT JOIN pg_am AS pa ON (pa.oid = pc.relam)
520 WHERE pc.relname LIKE 'tableam_%_heapx'
522 relkind | amname | relname
523 ---------+--------+-----------------------------
524 f | | tableam_fdw_heapx
525 r | heap2 | tableam_parted_1_heapx
526 r | heap | tableam_parted_2_heapx
527 p | | tableam_parted_heapx
528 S | | tableam_seq_heapx
529 r | heap2 | tableam_tbl_heapx
530 r | heap2 | tableam_tblas_heapx
531 m | heap2 | tableam_tblmv_heapx
532 r | heap2 | tableam_tblselectinto_heapx
533 v | | tableam_view_heapx
536 -- don't want to keep those tables, nor the default
538 -- Third, check that we can neither create a table using a nonexistent
539 -- AM, nor using an index AM
540 CREATE TABLE i_am_a_failure() USING "";
541 ERROR: zero-length delimited identifier at or near """"
542 LINE 1: CREATE TABLE i_am_a_failure() USING "";
544 CREATE TABLE i_am_a_failure() USING i_do_not_exist_am;
545 ERROR: access method "i_do_not_exist_am" does not exist
546 CREATE TABLE i_am_a_failure() USING "I do not exist AM";
547 ERROR: access method "I do not exist AM" does not exist
548 CREATE TABLE i_am_a_failure() USING "btree";
549 ERROR: access method "btree" is not of type TABLE
550 -- Other weird invalid cases that cause problems
551 CREATE FOREIGN TABLE fp PARTITION OF pg_am DEFAULT SERVER x;
552 ERROR: "pg_am" is not partitioned
553 -- Drop table access method, which fails as objects depends on it
554 DROP ACCESS METHOD heap2;
555 ERROR: cannot drop access method heap2 because other objects depend on it
556 DETAIL: table tableam_tbl_heap2 depends on access method heap2
557 table tableam_tblas_heap2 depends on access method heap2
558 materialized view tableam_tblmv_heap2 depends on access method heap2
559 table tableam_parted_b_heap2 depends on access method heap2
560 table tableam_parted_d_heap2 depends on access method heap2
561 HINT: Use DROP ... CASCADE to drop the dependent objects too.
562 -- we intentionally leave the objects created above alive, to verify pg_dump support