| -- |
| -- Create access method tests |
| -- |
| -- Make gist2 over gisthandler. In fact, it would be a synonym to gist. |
| CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler; |
| -- Verify return type checks for handlers |
| CREATE ACCESS METHOD bogus TYPE INDEX HANDLER int4in; |
| ERROR: function int4in(internal) does not exist |
| CREATE ACCESS METHOD bogus TYPE INDEX HANDLER heap_tableam_handler; |
| ERROR: function heap_tableam_handler must return type index_am_handler |
| -- Try to create gist2 index on fast_emp4000: fail because opclass doesn't exist |
| CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base); |
| ERROR: data type box has no default operator class for access method "gist2" |
| HINT: You must specify an operator class or define a default operator class for the data type. |
| -- Make operator class for boxes using gist2 |
| CREATE OPERATOR CLASS box_ops DEFAULT |
| FOR TYPE box USING gist2 AS |
| OPERATOR 1 <<, |
| OPERATOR 2 &<, |
| OPERATOR 3 &&, |
| OPERATOR 4 &>, |
| OPERATOR 5 >>, |
| OPERATOR 6 ~=, |
| OPERATOR 7 @>, |
| OPERATOR 8 <@, |
| OPERATOR 9 &<|, |
| OPERATOR 10 <<|, |
| OPERATOR 11 |>>, |
| OPERATOR 12 |&>, |
| FUNCTION 1 gist_box_consistent(internal, box, smallint, oid, internal), |
| FUNCTION 2 gist_box_union(internal, internal), |
| -- don't need compress, decompress, or fetch functions |
| FUNCTION 5 gist_box_penalty(internal, internal, internal), |
| FUNCTION 6 gist_box_picksplit(internal, internal), |
| FUNCTION 7 gist_box_same(box, box, internal); |
| -- Create gist2 index on fast_emp4000 |
| CREATE INDEX grect2ind2 ON fast_emp4000 USING gist2 (home_base); |
| -- Now check the results from plain indexscan; temporarily drop existing |
| -- index grect2ind to ensure it doesn't capture the plan |
| BEGIN; |
| DROP INDEX grect2ind; |
| SET enable_seqscan = OFF; |
| SET enable_indexscan = ON; |
| SET enable_bitmapscan = OFF; |
| EXPLAIN (COSTS OFF) |
| SELECT * FROM fast_emp4000 |
| WHERE home_base <@ '(200,200),(2000,1000)'::box |
| ORDER BY (home_base[0])[0]; |
| QUERY PLAN |
| ------------------------------------------------------------------------- |
| Result |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: ((home_base[0])[0]) |
| -> Sort |
| Sort Key: ((home_base[0])[0]) |
| -> Seq Scan on fast_emp4000 |
| Filter: (home_base <@ '(2000,1000),(200,200)'::box) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (8 rows) |
| |
| SELECT * FROM fast_emp4000 |
| WHERE home_base <@ '(200,200),(2000,1000)'::box |
| ORDER BY (home_base[0])[0]; |
| home_base |
| ----------------------- |
| (337,455),(240,359) |
| (1444,403),(1346,344) |
| (2 rows) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; |
| QUERY PLAN |
| --------------------------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Seq Scan on fast_emp4000 |
| Filter: (home_base && '(1000,1000),(0,0)'::box) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 rows) |
| |
| SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| EXPLAIN (COSTS OFF) |
| SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; |
| QUERY PLAN |
| ------------------------------------------------- |
| Finalize Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Partial Aggregate |
| -> Seq Scan on fast_emp4000 |
| Filter: (home_base IS NULL) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 rows) |
| |
| SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; |
| count |
| ------- |
| 278 |
| (1 row) |
| |
| ROLLBACK; |
| -- Try to drop access method: fail because of dependent objects |
| DROP ACCESS METHOD gist2; |
| ERROR: cannot drop access method gist2 because other objects depend on it |
| DETAIL: index grect2ind2 depends on operator class box_ops for access method gist2 |
| HINT: Use DROP ... CASCADE to drop the dependent objects too. |
| -- Drop access method cascade |
| -- To prevent a (rare) deadlock against autovacuum, |
| -- we must lock the table that owns the index that will be dropped |
| BEGIN; |
| LOCK TABLE fast_emp4000; |
| DROP ACCESS METHOD gist2 CASCADE; |
| NOTICE: drop cascades to index grect2ind2 |
| COMMIT; |
| -- |
| -- Test table access methods |
| -- |
| -- prevent empty values |
| SET default_table_access_method = ''; |
| ERROR: invalid value for parameter "default_table_access_method": "" |
| DETAIL: default_table_access_method cannot be empty. |
| -- prevent nonexistent values |
| SET default_table_access_method = 'I do not exist AM'; |
| ERROR: invalid value for parameter "default_table_access_method": "I do not exist AM" |
| DETAIL: Table access method "I do not exist AM" does not exist. |
| -- prevent setting it to an index AM |
| SET default_table_access_method = 'btree'; |
| ERROR: access method "btree" is not of type TABLE |
| -- Create a heap2 table am handler with heapam handler |
| CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler; |
| -- Verify return type checks for handlers |
| CREATE ACCESS METHOD bogus TYPE TABLE HANDLER int4in; |
| ERROR: function int4in(internal) does not exist |
| CREATE ACCESS METHOD bogus TYPE TABLE HANDLER bthandler; |
| ERROR: function bthandler must return type table_am_handler |
| SELECT amname, amhandler, amtype FROM pg_am where amtype = 't' and amname not in ('pax') ORDER BY 1, 2; |
| amname | amhandler | amtype |
| -----------+---------------------------+-------- |
| ao_column | ao_column_tableam_handler | t |
| ao_row | ao_row_tableam_handler | t |
| heap | heap_tableam_handler | t |
| heap2 | heap_tableam_handler | t |
| (4 rows) |
| |
| -- First create tables employing the new AM using USING |
| -- plain CREATE TABLE |
| CREATE TABLE tableam_tbl_heap2(f1 int) USING heap2; |
| INSERT INTO tableam_tbl_heap2 VALUES(1); |
| SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1; |
| f1 |
| ---- |
| 1 |
| (1 row) |
| |
| -- CREATE TABLE AS |
| CREATE TABLE tableam_tblas_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; |
| SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1; |
| f1 |
| ---- |
| 1 |
| (1 row) |
| |
| -- SELECT INTO doesn't support USING |
| SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2; |
| ERROR: syntax error at or near "USING" |
| LINE 1: SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tab... |
| ^ |
| -- CREATE VIEW doesn't support USING |
| CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; |
| ERROR: syntax error at or near "USING" |
| LINE 1: CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM ... |
| ^ |
| -- CREATE SEQUENCE doesn't support USING |
| CREATE SEQUENCE tableam_seq_heap2 USING heap2; |
| ERROR: syntax error at or near "USING" |
| LINE 1: CREATE SEQUENCE tableam_seq_heap2 USING heap2; |
| ^ |
| -- CREATE MATERIALIZED VIEW does support USING |
| CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; |
| SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1; |
| f1 |
| ---- |
| 1 |
| (1 row) |
| |
| -- CREATE TABLE .. PARTITION BY doesn't not support USING |
| -- CBDB ignore: allow table access method |
| -- CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2; |
| CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a); |
| -- new partitions will inherit from the current default, rather the partition root |
| SET default_table_access_method = 'heap'; |
| CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a'); |
| SET default_table_access_method = 'heap2'; |
| CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b'); |
| RESET default_table_access_method; |
| -- but the method can be explicitly specified |
| CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap; |
| CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2; |
| -- List all objects in AM |
| SELECT |
| pc.relkind, |
| pa.amname, |
| CASE WHEN relkind = 't' THEN |
| (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid) |
| ELSE |
| relname::regclass::text |
| END COLLATE "C" AS relname |
| FROM pg_class AS pc, |
| pg_am AS pa |
| WHERE pa.oid = pc.relam |
| AND pa.amname = 'heap2' |
| ORDER BY 3, 1, 2; |
| relkind | amname | relname |
| ---------+--------+---------------------------------- |
| r | heap2 | tableam_parted_b_heap2 |
| r | heap2 | tableam_parted_d_heap2 |
| r | heap2 | tableam_tbl_heap2 |
| r | heap2 | tableam_tblas_heap2 |
| m | heap2 | tableam_tblmv_heap2 |
| t | heap2 | toast for tableam_parted_b_heap2 |
| t | heap2 | toast for tableam_parted_d_heap2 |
| (7 rows) |
| |
| -- Show dependencies onto AM - there shouldn't be any for toast |
| SELECT pg_describe_object(classid,objid,objsubid) AS obj |
| FROM pg_depend, pg_am |
| WHERE pg_depend.refclassid = 'pg_am'::regclass |
| AND pg_am.oid = pg_depend.refobjid |
| AND pg_am.amname = 'heap2' |
| ORDER BY classid, objid, objsubid; |
| obj |
| --------------------------------------- |
| table tableam_tbl_heap2 |
| table tableam_tblas_heap2 |
| materialized view tableam_tblmv_heap2 |
| table tableam_parted_b_heap2 |
| table tableam_parted_d_heap2 |
| (5 rows) |
| |
| -- ALTER TABLE SET ACCESS METHOD |
| CREATE TABLE heaptable USING heap AS |
| SELECT a, repeat(a::text, 100) FROM generate_series(1,9) AS a; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| SELECT amname FROM pg_class c, pg_am am |
| WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass; |
| amname |
| -------- |
| heap |
| (1 row) |
| |
| ALTER TABLE heaptable SET ACCESS METHOD heap2; |
| SELECT amname FROM pg_class c, pg_am am |
| WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass; |
| amname |
| -------- |
| heap2 |
| (1 row) |
| |
| SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable; |
| count | count |
| -------+------- |
| 9 | 1 |
| (1 row) |
| |
| -- No support for multiple subcommands |
| ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2; |
| ERROR: cannot have multiple SET ACCESS METHOD subcommands |
| DROP TABLE heaptable; |
| -- No support for partitioned tables. |
| CREATE TABLE am_partitioned(x INT, y INT) |
| PARTITION BY hash (x); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| ALTER TABLE am_partitioned SET ACCESS METHOD heap2; |
| ERROR: cannot change access method of a partitioned table |
| DROP TABLE am_partitioned; |
| -- Second, create objects in the new AM by changing the default AM |
| BEGIN; |
| SET LOCAL default_table_access_method = 'heap2'; |
| -- following tests should all respect the default AM |
| CREATE TABLE tableam_tbl_heapx(f1 int); |
| CREATE TABLE tableam_tblas_heapx AS SELECT * FROM tableam_tbl_heapx; |
| SELECT INTO tableam_tblselectinto_heapx FROM tableam_tbl_heapx; |
| CREATE MATERIALIZED VIEW tableam_tblmv_heapx USING heap2 AS SELECT * FROM tableam_tbl_heapx; |
| CREATE TABLE tableam_parted_heapx (a text, b int) PARTITION BY list (a); |
| CREATE TABLE tableam_parted_1_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('a', 'b'); |
| -- but an explicitly set AM overrides it |
| CREATE TABLE tableam_parted_2_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('c', 'd') USING heap; |
| -- sequences, views and foreign servers shouldn't have an AM |
| CREATE VIEW tableam_view_heapx AS SELECT * FROM tableam_tbl_heapx; |
| CREATE SEQUENCE tableam_seq_heapx; |
| CREATE FOREIGN DATA WRAPPER fdw_heap2 VALIDATOR postgresql_fdw_validator; |
| CREATE SERVER fs_heap2 FOREIGN DATA WRAPPER fdw_heap2 ; |
| CREATE FOREIGN table tableam_fdw_heapx () SERVER fs_heap2; |
| -- Verify that new AM was used for tables, matviews, but not for sequences, views and fdws |
| SELECT |
| pc.relkind, |
| pa.amname, |
| CASE WHEN relkind = 't' THEN |
| (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid) |
| ELSE |
| relname::regclass::text |
| END COLLATE "C" AS relname |
| FROM pg_class AS pc |
| LEFT JOIN pg_am AS pa ON (pa.oid = pc.relam) |
| WHERE pc.relname LIKE 'tableam_%_heapx' |
| ORDER BY 3, 1, 2; |
| relkind | amname | relname |
| ---------+--------+----------------------------- |
| f | | tableam_fdw_heapx |
| r | heap2 | tableam_parted_1_heapx |
| r | heap | tableam_parted_2_heapx |
| p | | tableam_parted_heapx |
| S | | tableam_seq_heapx |
| r | heap2 | tableam_tbl_heapx |
| r | heap2 | tableam_tblas_heapx |
| m | heap2 | tableam_tblmv_heapx |
| r | heap2 | tableam_tblselectinto_heapx |
| v | | tableam_view_heapx |
| (10 rows) |
| |
| -- don't want to keep those tables, nor the default |
| ROLLBACK; |
| -- Third, check that we can neither create a table using a nonexistent |
| -- AM, nor using an index AM |
| CREATE TABLE i_am_a_failure() USING ""; |
| ERROR: zero-length delimited identifier at or near """" |
| LINE 1: CREATE TABLE i_am_a_failure() USING ""; |
| ^ |
| CREATE TABLE i_am_a_failure() USING i_do_not_exist_am; |
| ERROR: access method "i_do_not_exist_am" does not exist |
| CREATE TABLE i_am_a_failure() USING "I do not exist AM"; |
| ERROR: access method "I do not exist AM" does not exist |
| CREATE TABLE i_am_a_failure() USING "btree"; |
| ERROR: access method "btree" is not of type TABLE |
| -- Drop table access method, which fails as objects depends on it |
| DROP ACCESS METHOD heap2; |
| ERROR: cannot drop access method heap2 because other objects depend on it |
| DETAIL: table tableam_tbl_heap2 depends on access method heap2 |
| table tableam_tblas_heap2 depends on access method heap2 |
| materialized view tableam_tblmv_heap2 depends on access method heap2 |
| table tableam_parted_b_heap2 depends on access method heap2 |
| table tableam_parted_d_heap2 depends on access method heap2 |
| HINT: Use DROP ... CASCADE to drop the dependent objects too. |
| -- we intentionally leave the objects created above alive, to verify pg_dump support |
