summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/create_am.out
blob: b50293d514f8df9593144e5f6b74ea2232d10f8e (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
--
-- 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 doesn't not support USING
CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
ERROR:  specifying a table access method is not supported on a partitioned table
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)

-- 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 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;
-- No support for partitioned tables.
CREATE TABLE am_partitioned(x INT, y INT)
  PARTITION BY hash (x);
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