src/test/regress/expected/create_am.out

   1 --
   2 -- Create access method tests
   3 --
   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
  18         OPERATOR 1      <<,
  19         OPERATOR 2      &<,
  20         OPERATOR 3      &&,
  21         OPERATOR 4      &>,
  22         OPERATOR 5      >>,
  23         OPERATOR 6      ~=,
  24         OPERATOR 7      @>,
  25         OPERATOR 8      <@,
  26         OPERATOR 9      &<|,
  27         OPERATOR 10     <<|,
  28         OPERATOR 11     |>>,
  29         OPERATOR 12     |&>,
  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
  40 BEGIN;
  41 DROP INDEX grect2ind;
  42 SET enable_seqscan = OFF;
  43 SET enable_indexscan = ON;
  44 SET enable_bitmapscan = OFF;
  45 EXPLAIN (COSTS OFF)
  46 SELECT * FROM fast_emp4000
  47     WHERE home_base <@ '(200,200),(2000,1000)'::box
  48     ORDER BY (home_base[0])[0];
  49                            QUERY PLAN
  50 -----------------------------------------------------------------
  51  Sort
  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)
  55 (4 rows)
  56
  57 SELECT * FROM fast_emp4000
  58     WHERE home_base <@ '(200,200),(2000,1000)'::box
  59     ORDER BY (home_base[0])[0];
  60        home_base
  61 -----------------------
  62  (337,455),(240,359)
  63  (1444,403),(1346,344)
  64 (2 rows)
  65
  66 EXPLAIN (COSTS OFF)
  67 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  68                          QUERY PLAN
  69 -------------------------------------------------------------
  70  Aggregate
  71    ->  Index Only Scan using grect2ind2 on fast_emp4000
  72          Index Cond: (home_base && '(1000,1000),(0,0)'::box)
  73 (3 rows)
  74
  75 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  76  count
  77 -------
  78      2
  79 (1 row)
  80
  81 EXPLAIN (COSTS OFF)
  82 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
  83                        QUERY PLAN
  84 --------------------------------------------------------
  85  Aggregate
  86    ->  Index Only Scan using grect2ind2 on fast_emp4000
  87          Index Cond: (home_base IS NULL)
  88 (3 rows)
  89
  90 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
  91  count
  92 -------
  93    278
  94 (1 row)
  95
  96 ROLLBACK;
  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
 105 BEGIN;
 106 LOCK TABLE fast_emp4000;
 107 DROP ACCESS METHOD gist2 CASCADE;
 108 NOTICE:  drop cascades to index grect2ind2
 109 COMMIT;
 110 --
 111 -- Test table access methods
 112 --
 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
 136 (2 rows)
 137
 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;
 143  f1
 144 ----
 145   1
 146 (1 row)
 147
 148 -- CREATE TABLE AS
 149 CREATE TABLE tableam_tblas_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
 150 SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1;
 151  f1
 152 ----
 153   1
 154 (1 row)
 155
 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...
 160                                                 ^
 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 ...
 165                                        ^
 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;
 170                                           ^
 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;
 174  f1
 175 ----
 176   1
 177 (1 row)
 178
 179 -- CREATE TABLE ..  PARTITION BY doesn't not support USING
 180 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
 181 ERROR:  specifying a table access method is not supported on a partitioned table
 182 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
 183 -- new partitions will inherit from the current default, rather the partition root
 184 SET default_table_access_method = 'heap';
 185 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
 186 SET default_table_access_method = 'heap2';
 187 CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b');
 188 RESET default_table_access_method;
 189 -- but the method can be explicitly specified
 190 CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap;
 191 CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2;
 192 -- List all objects in AM
 193 SELECT
 194     pc.relkind,
 195     pa.amname,
 196     CASE WHEN relkind = 't' THEN
 197         (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid)
 198     ELSE
 199         relname::regclass::text
 200     END COLLATE "C" AS relname
 201 FROM pg_class AS pc,
 202     pg_am AS pa
 203 WHERE pa.oid = pc.relam
 204    AND pa.amname = 'heap2'
 205 ORDER BY 3, 1, 2;
 206  relkind | amname |             relname
 207 ---------+--------+----------------------------------
 208  r       | heap2  | tableam_parted_b_heap2
 209  r       | heap2  | tableam_parted_d_heap2
 210  r       | heap2  | tableam_tbl_heap2
 211  r       | heap2  | tableam_tblas_heap2
 212  m       | heap2  | tableam_tblmv_heap2
 213  t       | heap2  | toast for tableam_parted_b_heap2
 214  t       | heap2  | toast for tableam_parted_d_heap2
 215 (7 rows)
 216
 217 -- Show dependencies onto AM - there shouldn't be any for toast
 218 SELECT pg_describe_object(classid,objid,objsubid) AS obj
 219 FROM pg_depend, pg_am
 220 WHERE pg_depend.refclassid = 'pg_am'::regclass
 221     AND pg_am.oid = pg_depend.refobjid
 222     AND pg_am.amname = 'heap2'
 223 ORDER BY classid, objid, objsubid;
 224                   obj
 225 ---------------------------------------
 226  table tableam_tbl_heap2
 227  table tableam_tblas_heap2
 228  materialized view tableam_tblmv_heap2
 229  table tableam_parted_b_heap2
 230  table tableam_parted_d_heap2
 231 (5 rows)
 232
 233 -- ALTER TABLE SET ACCESS METHOD
 234 CREATE TABLE heaptable USING heap AS
 235   SELECT a, repeat(a::text, 100) FROM generate_series(1,9) AS a;
 236 SELECT amname FROM pg_class c, pg_am am
 237   WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 238  amname
 239 --------
 240  heap
 241 (1 row)
 242
 243 ALTER TABLE heaptable SET ACCESS METHOD heap2;
 244 SELECT amname FROM pg_class c, pg_am am
 245   WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 246  amname
 247 --------
 248  heap2
 249 (1 row)
 250
 251 SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
 252  count | count
 253 -------+-------
 254      9 |     1
 255 (1 row)
 256
 257 -- No support for multiple subcommands
 258 ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 259 ERROR:  cannot have multiple SET ACCESS METHOD subcommands
 260 DROP TABLE heaptable;
 261 -- No support for partitioned tables.
 262 CREATE TABLE am_partitioned(x INT, y INT)
 263   PARTITION BY hash (x);
 264 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
 265 ERROR:  cannot change access method of a partitioned table
 266 DROP TABLE am_partitioned;
 267 -- Second, create objects in the new AM by changing the default AM
 268 BEGIN;
 269 SET LOCAL default_table_access_method = 'heap2';
 270 -- following tests should all respect the default AM
 271 CREATE TABLE tableam_tbl_heapx(f1 int);
 272 CREATE TABLE tableam_tblas_heapx AS SELECT * FROM tableam_tbl_heapx;
 273 SELECT INTO tableam_tblselectinto_heapx FROM tableam_tbl_heapx;
 274 CREATE MATERIALIZED VIEW tableam_tblmv_heapx USING heap2 AS SELECT * FROM tableam_tbl_heapx;
 275 CREATE TABLE tableam_parted_heapx (a text, b int) PARTITION BY list (a);
 276 CREATE TABLE tableam_parted_1_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('a', 'b');
 277 -- but an explicitly set AM overrides it
 278 CREATE TABLE tableam_parted_2_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('c', 'd') USING heap;
 279 -- sequences, views and foreign servers shouldn't have an AM
 280 CREATE VIEW tableam_view_heapx AS SELECT * FROM tableam_tbl_heapx;
 281 CREATE SEQUENCE tableam_seq_heapx;
 282 CREATE FOREIGN DATA WRAPPER fdw_heap2 VALIDATOR postgresql_fdw_validator;
 283 CREATE SERVER fs_heap2 FOREIGN DATA WRAPPER fdw_heap2 ;
 284 CREATE FOREIGN table tableam_fdw_heapx () SERVER fs_heap2;
 285 -- Verify that new AM was used for tables, matviews, but not for sequences, views and fdws
 286 SELECT
 287     pc.relkind,
 288     pa.amname,
 289     CASE WHEN relkind = 't' THEN
 290         (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid)
 291     ELSE
 292         relname::regclass::text
 293     END COLLATE "C" AS relname
 294 FROM pg_class AS pc
 295     LEFT JOIN pg_am AS pa ON (pa.oid = pc.relam)
 296 WHERE pc.relname LIKE 'tableam_%_heapx'
 297 ORDER BY 3, 1, 2;
 298  relkind | amname |           relname
 299 ---------+--------+-----------------------------
 300  f       |        | tableam_fdw_heapx
 301  r       | heap2  | tableam_parted_1_heapx
 302  r       | heap   | tableam_parted_2_heapx
 303  p       |        | tableam_parted_heapx
 304  S       |        | tableam_seq_heapx
 305  r       | heap2  | tableam_tbl_heapx
 306  r       | heap2  | tableam_tblas_heapx
 307  m       | heap2  | tableam_tblmv_heapx
 308  r       | heap2  | tableam_tblselectinto_heapx
 309  v       |        | tableam_view_heapx
 310 (10 rows)
 311
 312 -- don't want to keep those tables, nor the default
 313 ROLLBACK;
 314 -- Third, check that we can neither create a table using a nonexistent
 315 -- AM, nor using an index AM
 316 CREATE TABLE i_am_a_failure() USING "";
 317 ERROR:  zero-length delimited identifier at or near """"
 318 LINE 1: CREATE TABLE i_am_a_failure() USING "";
 319                                             ^
 320 CREATE TABLE i_am_a_failure() USING i_do_not_exist_am;
 321 ERROR:  access method "i_do_not_exist_am" does not exist
 322 CREATE TABLE i_am_a_failure() USING "I do not exist AM";
 323 ERROR:  access method "I do not exist AM" does not exist
 324 CREATE TABLE i_am_a_failure() USING "btree";
 325 ERROR:  access method "btree" is not of type TABLE
 326 -- Drop table access method, which fails as objects depends on it
 327 DROP ACCESS METHOD heap2;
 328 ERROR:  cannot drop access method heap2 because other objects depend on it
 329 DETAIL:  table tableam_tbl_heap2 depends on access method heap2
 330 table tableam_tblas_heap2 depends on access method heap2
 331 materialized view tableam_tblmv_heap2 depends on access method heap2
 332 table tableam_parted_b_heap2 depends on access method heap2
 333 table tableam_parted_d_heap2 depends on access method heap2
 334 HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 335 -- we intentionally leave the objects created above alive, to verify pg_dump support