Allow specifying an access method for partitioned tables

[pgsql.git] / src / test / regress / expected / create_am.out

--
-- 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 for the index 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                            
-----------------------------------------------------------------
 Sort
   Sort Key: ((home_base[0])[0])
   ->  Index Only Scan using grect2ind2 on fast_emp4000
         Index Cond: (home_base <@ '(2000,1000),(200,200)'::box)
(4 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                          
-------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using grect2ind2 on fast_emp4000
         Index Cond: (home_base && '(1000,1000),(0,0)'::box)
(3 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                       
--------------------------------------------------------
 Aggregate
   ->  Index Only Scan using grect2ind2 on fast_emp4000
         Index Cond: (home_base IS NULL)
(3 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' ORDER BY 1, 2;
 amname |      amhandler       | amtype 
--------+----------------------+--------
 heap   | heap_tableam_handler | t
 heap2  | heap_tableam_handler | t
(2 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 supports USING.
CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
SELECT a.amname FROM pg_class c, pg_am a
  WHERE c.relname = 'tableam_parted_heap2' AND a.oid = c.relam;
 amname 
--------
 heap2
(1 row)

DROP TABLE tableam_parted_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;
SELECT amname FROM pg_class c, pg_am am
  WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 amname 
--------
 heap
(1 row)

-- Switching to heap2 adds new dependency entry to the AM.
ALTER TABLE heaptable SET ACCESS METHOD heap2;
SELECT pg_describe_object(classid, objid, objsubid) as obj,
       pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
       deptype
  FROM pg_depend
  WHERE classid = 'pg_class'::regclass AND
        objid = 'heaptable'::regclass
  ORDER BY 1, 2;
       obj       |       objref        | deptype 
-----------------+---------------------+---------
 table heaptable | access method heap2 | n
 table heaptable | schema public       | n
(2 rows)

-- Switching to heap should not have a dependency entry to the AM.
ALTER TABLE heaptable SET ACCESS METHOD heap;
SELECT pg_describe_object(classid, objid, objsubid) as obj,
       pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
       deptype
  FROM pg_depend
  WHERE classid = 'pg_class'::regclass AND
        objid = 'heaptable'::regclass
  ORDER BY 1, 2;
       obj       |    objref     | deptype 
-----------------+---------------+---------
 table heaptable | schema public | n
(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)

-- DEFAULT access method
BEGIN;
SET LOCAL default_table_access_method TO heap2;
ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
SELECT amname FROM pg_class c, pg_am am
  WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 amname 
--------
 heap2
(1 row)

SET LOCAL default_table_access_method TO heap;
ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
SELECT amname FROM pg_class c, pg_am am
  WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 amname 
--------
 heap
(1 row)

ROLLBACK;
-- ALTER MATERIALIZED VIEW SET ACCESS METHOD
CREATE MATERIALIZED VIEW heapmv USING heap AS SELECT * FROM heaptable;
SELECT amname FROM pg_class c, pg_am am
  WHERE c.relam = am.oid AND c.oid = 'heapmv'::regclass;
 amname 
--------
 heap
(1 row)

ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap2;
SELECT amname FROM pg_class c, pg_am am
  WHERE c.relam = am.oid AND c.oid = 'heapmv'::regclass;
 amname 
--------
 heap2
(1 row)

SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heapmv;
 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
ALTER TABLE heaptable SET ACCESS METHOD DEFAULT, SET ACCESS METHOD heap2;
ERROR:  cannot have multiple SET ACCESS METHOD subcommands
ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
ERROR:  cannot have multiple SET ACCESS METHOD subcommands
DROP MATERIALIZED VIEW heapmv;
DROP TABLE heaptable;
-- Partition hierarchies with access methods
BEGIN;
SET LOCAL default_table_access_method = 'heap';
CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
-- pg_class.relam is 0, no dependency recorded between the AM and the
-- partitioned table.
SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
 relam 
-------
     0
(1 row)

SELECT pg_describe_object(classid, objid, objsubid) AS obj,
       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
  FROM pg_depend, pg_am
  WHERE pg_depend.refclassid = 'pg_am'::regclass
    AND pg_am.oid = pg_depend.refobjid
    AND pg_depend.objid = 'am_partitioned'::regclass;
 obj | refobj 
-----+--------
(0 rows)

-- New default is set, with dependency added.
ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
SELECT a.amname FROM pg_class c, pg_am a
  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
 amname 
--------
 heap2
(1 row)

SELECT pg_describe_object(classid, objid, objsubid) AS obj,
       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
  FROM pg_depend, pg_am
  WHERE pg_depend.refclassid = 'pg_am'::regclass
    AND pg_am.oid = pg_depend.refobjid
    AND pg_depend.objid = 'am_partitioned'::regclass;
         obj          |       refobj        
----------------------+---------------------
 table am_partitioned | access method heap2
(1 row)

-- Default is set, with dependency updated.
SET LOCAL default_table_access_method = 'heap2';
ALTER TABLE am_partitioned SET ACCESS METHOD heap;
SELECT a.amname FROM pg_class c, pg_am a
  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
 amname 
--------
 heap
(1 row)

-- Dependency pinned, hence removed.
SELECT pg_describe_object(classid, objid, objsubid) AS obj,
       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
  FROM pg_depend, pg_am
  WHERE pg_depend.refclassid = 'pg_am'::regclass
    AND pg_am.oid = pg_depend.refobjid
    AND pg_depend.objid = 'am_partitioned'::regclass;
 obj | refobj 
-----+--------
(0 rows)

-- Default and AM set in the clause are the same, relam should be set.
SET LOCAL default_table_access_method = 'heap2';
ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
SELECT a.amname FROM pg_class c, pg_am a
  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
 amname 
--------
 heap2
(1 row)

-- Reset to default
ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
 relam 
-------
     0
(1 row)

-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
-- will inherit the AM set.  Existing partitioned are unchanged.
SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
 relam 
-------
     0
(1 row)

SET LOCAL default_table_access_method = 'heap';
CREATE TABLE am_partitioned_0 PARTITION OF am_partitioned
  FOR VALUES WITH (MODULUS 10, REMAINDER 0);
SET LOCAL default_table_access_method = 'heap2';
CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned
  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
SET LOCAL default_table_access_method = 'heap';
ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned
  FOR VALUES WITH (MODULUS 10, REMAINDER 2);
ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
 relam 
-------
     0
(1 row)

CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned
  FOR VALUES WITH (MODULUS 10, REMAINDER 3);
-- Partitioned table with relam at 0
ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
CREATE TABLE am_partitioned_5p PARTITION OF am_partitioned
  FOR VALUES WITH (MODULUS 10, REMAINDER 5) PARTITION BY hash(y);
-- Partitions of this partitioned table inherit default AM at creation
-- time.
CREATE TABLE am_partitioned_5p1 PARTITION OF am_partitioned_5p
  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
-- Partitioned table with relam set.
ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
CREATE TABLE am_partitioned_6p PARTITION OF am_partitioned
  FOR VALUES WITH (MODULUS 10, REMAINDER 6) PARTITION BY hash(y);
-- Partitions of this partitioned table inherit its AM.
CREATE TABLE am_partitioned_6p1 PARTITION OF am_partitioned_6p
  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
SELECT c.relname, a.amname FROM pg_class c, pg_am a
  WHERE c.relam = a.oid AND
        c.relname LIKE 'am_partitioned%'
UNION ALL
SELECT c.relname, 'default' FROM pg_class c
  WHERE c.relam = 0
        AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
      relname       | amname  
--------------------+---------
 am_partitioned     | heap2
 am_partitioned_0   | heap
 am_partitioned_1   | heap2
 am_partitioned_2   | heap2
 am_partitioned_3   | heap
 am_partitioned_5p  | default
 am_partitioned_5p1 | heap
 am_partitioned_6p  | heap2
 am_partitioned_6p1 | heap2
(9 rows)

DROP TABLE am_partitioned;
COMMIT;
-- 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