2 -- Create access method tests
5 -- Make gist2 over gisthandler. In fact, it would be a synonym to gist.
6 CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;
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;
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);
16 -- Make operator class for boxes using gist2
17 CREATE OPERATOR CLASS box_ops DEFAULT
18 FOR TYPE box USING gist2 AS
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);
38 -- Create gist2 index on fast_emp4000
39 CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base);
41 -- Now check the results from plain indexscan; temporarily drop existing
42 -- index grect2ind to ensure it doesn't capture the plan
45 SET enable_seqscan = OFF;
46 SET enable_indexscan = ON;
47 SET enable_bitmapscan = 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];
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;
62 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
63 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
67 -- Try to drop access method: fail because of dependent objects
68 DROP ACCESS METHOD gist2;
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
74 LOCK TABLE fast_emp4000;
75 DROP ACCESS METHOD gist2 CASCADE;
80 -- Test table access methods
83 -- prevent empty values
84 SET default_table_access_method = '';
86 -- prevent nonexistent values
87 SET default_table_access_method = 'I do not exist AM';
89 -- prevent setting it to an index AM
90 SET default_table_access_method = 'btree';
93 -- Create a heap2 table am handler with heapam handler
94 CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;
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;
100 SELECT amname, amhandler, amtype FROM pg_am where amtype = 't' ORDER BY 1, 2;
103 -- First create tables employing the new AM using USING
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;
111 CREATE TABLE tableam_tblas_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
112 SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1;
114 -- SELECT INTO doesn't support USING
115 SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2;
117 -- CREATE VIEW doesn't support USING
118 CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
120 -- CREATE SEQUENCE doesn't support USING
121 CREATE SEQUENCE tableam_seq_heap2 USING heap2;
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;
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;
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;
144 -- List all objects in AM
148 CASE WHEN relkind = 't' THEN
149 (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid)
151 relname::regclass::text
152 END COLLATE "C" AS relname
155 WHERE pa.oid = pc.relam
156 AND pa.amname = 'heap2'
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;
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,
178 WHERE classid = 'pg_class'::regclass AND
179 objid = 'heaptable'::regclass
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,
187 WHERE classid = 'pg_class'::regclass AND
188 objid = 'heaptable'::regclass
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
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;
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;
220 -- Partition hierarchies with access methods
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;
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
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%'
299 SELECT c.relname, 'default' FROM pg_class c
301 AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
302 DROP TABLE am_partitioned;
305 -- Second, create objects in the new AM by changing the default AM
307 SET LOCAL default_table_access_method = 'heap2';
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');
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;
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;
327 -- Verify that new AM was used for tables, matviews, but not for sequences, views and fdws
331 CASE WHEN relkind = 't' THEN
332 (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid)
334 relname::regclass::text
335 END COLLATE "C" AS relname
337 LEFT JOIN pg_am AS pa ON (pa.oid = pc.relam)
338 WHERE pc.relname LIKE 'tableam_%_heapx'
341 -- don't want to keep those tables, nor the default
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";
351 -- Other weird invalid cases that cause problems
352 CREATE FOREIGN TABLE fp PARTITION OF tableam_parted_a_heap2 DEFAULT SERVER x;
354 -- Drop table access method, which fails as objects depends on it
355 DROP ACCESS METHOD heap2;
357 -- we intentionally leave the objects created above alive, to verify pg_dump support