summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/temp.out
blob: 2a246a7e1231a581c8891c569cbeb971d9e7ec7b (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
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
--
-- TEMP
-- Test temp relations and indexes
--
-- test temp table/index masking
CREATE TABLE temptest(col int);
CREATE INDEX i_temptest ON temptest(col);
CREATE TEMP TABLE temptest(tcol int);
CREATE INDEX i_temptest ON temptest(tcol);
SELECT * FROM temptest;
 tcol 
------
(0 rows)

DROP INDEX i_temptest;
DROP TABLE temptest;
SELECT * FROM temptest;
 col 
-----
(0 rows)

DROP INDEX i_temptest;
DROP TABLE temptest;
-- test temp table selects
CREATE TABLE temptest(col int);
INSERT INTO temptest VALUES (1);
CREATE TEMP TABLE temptest(tcol float);
INSERT INTO temptest VALUES (2.1);
SELECT * FROM temptest;
 tcol 
------
  2.1
(1 row)

DROP TABLE temptest;
SELECT * FROM temptest;
 col 
-----
   1
(1 row)

DROP TABLE temptest;
-- test temp table deletion
CREATE TEMP TABLE temptest(col int);
\c
SELECT * FROM temptest;
ERROR:  relation "temptest" does not exist
LINE 1: SELECT * FROM temptest;
                      ^
-- Test ON COMMIT DELETE ROWS
CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
-- while we're here, verify successful truncation of index with SQL function
CREATE INDEX ON temptest(bit_length(''));
BEGIN;
INSERT INTO temptest VALUES (1);
INSERT INTO temptest VALUES (2);
SELECT * FROM temptest;
 col 
-----
   1
   2
(2 rows)

COMMIT;
SELECT * FROM temptest;
 col 
-----
(0 rows)

DROP TABLE temptest;
BEGIN;
CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
SELECT * FROM temptest;
 col 
-----
   1
(1 row)

COMMIT;
SELECT * FROM temptest;
 col 
-----
(0 rows)

DROP TABLE temptest;
-- Test ON COMMIT DROP
BEGIN;
CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
INSERT INTO temptest VALUES (1);
INSERT INTO temptest VALUES (2);
SELECT * FROM temptest;
 col 
-----
   1
   2
(2 rows)

COMMIT;
SELECT * FROM temptest;
ERROR:  relation "temptest" does not exist
LINE 1: SELECT * FROM temptest;
                      ^
BEGIN;
CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1;
SELECT * FROM temptest;
 col 
-----
   1
(1 row)

COMMIT;
SELECT * FROM temptest;
ERROR:  relation "temptest" does not exist
LINE 1: SELECT * FROM temptest;
                      ^
-- Test it with a CHECK condition that produces a toasted pg_constraint entry
BEGIN;
do $$
begin
  execute format($cmd$
    CREATE TEMP TABLE temptest (col text CHECK (col < %L)) ON COMMIT DROP
  $cmd$,
    (SELECT string_agg(g.i::text || ':' || random()::text, '|')
     FROM generate_series(1, 100) g(i)));
end$$;
SELECT * FROM temptest;
 col 
-----
(0 rows)

COMMIT;
SELECT * FROM temptest;
ERROR:  relation "temptest" does not exist
LINE 1: SELECT * FROM temptest;
                      ^
-- ON COMMIT is only allowed for TEMP
CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
ERROR:  ON COMMIT can only be used on temporary tables
CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
ERROR:  ON COMMIT can only be used on temporary tables
-- Test foreign keys
BEGIN;
CREATE TEMP TABLE temptest1(col int PRIMARY KEY);
CREATE TEMP TABLE temptest2(col int REFERENCES temptest1)
  ON COMMIT DELETE ROWS;
INSERT INTO temptest1 VALUES (1);
INSERT INTO temptest2 VALUES (1);
COMMIT;
SELECT * FROM temptest1;
 col 
-----
   1
(1 row)

SELECT * FROM temptest2;
 col 
-----
(0 rows)

BEGIN;
CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS;
CREATE TEMP TABLE temptest4(col int REFERENCES temptest3);
COMMIT;
ERROR:  unsupported ON COMMIT and foreign key combination
DETAIL:  Table "temptest4" references "temptest3", but they do not have the same ON COMMIT setting.
-- Test manipulation of temp schema's placement in search path
create table public.whereami (f1 text);
insert into public.whereami values ('public');
create temp table whereami (f1 text);
insert into whereami values ('temp');
create function public.whoami() returns text
  as $$select 'public'::text$$ language sql;
create function pg_temp.whoami() returns text
  as $$select 'temp'::text$$ language sql;
-- default should have pg_temp implicitly first, but only for tables
select * from whereami;
  f1  
------
 temp
(1 row)

select whoami();
 whoami 
--------
 public
(1 row)

-- can list temp first explicitly, but it still doesn't affect functions
set search_path = pg_temp, public;
select * from whereami;
  f1  
------
 temp
(1 row)

select whoami();
 whoami 
--------
 public
(1 row)

-- or put it last for security
set search_path = public, pg_temp;
select * from whereami;
   f1   
--------
 public
(1 row)

select whoami();
 whoami 
--------
 public
(1 row)

-- you can invoke a temp function explicitly, though
select pg_temp.whoami();
 whoami 
--------
 temp
(1 row)

drop table public.whereami;
-- types in temp schema
set search_path = pg_temp, public;
create domain pg_temp.nonempty as text check (value <> '');
-- function-syntax invocation of types matches rules for functions
select nonempty('');
ERROR:  function nonempty(unknown) does not exist
LINE 1: select nonempty('');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
select pg_temp.nonempty('');
ERROR:  value for domain nonempty violates check constraint "nonempty_check"
-- other syntax matches rules for tables
select ''::nonempty;
ERROR:  value for domain nonempty violates check constraint "nonempty_check"
reset search_path;
-- For partitioned temp tables, ON COMMIT actions ignore storage-less
-- partitioned tables.
begin;
create temp table temp_parted_oncommit (a int)
  partition by list (a) on commit delete rows;
create temp table temp_parted_oncommit_1
  partition of temp_parted_oncommit
  for values in (1) on commit delete rows;
insert into temp_parted_oncommit values (1);
commit;
-- partitions are emptied by the previous commit
select * from temp_parted_oncommit;
 a 
---
(0 rows)

drop table temp_parted_oncommit;
-- Check dependencies between ON COMMIT actions with a partitioned
-- table and its partitions.  Using ON COMMIT DROP on a parent removes
-- the whole set.
begin;
create temp table temp_parted_oncommit_test (a int)
  partition by list (a) on commit drop;
create temp table temp_parted_oncommit_test1
  partition of temp_parted_oncommit_test
  for values in (1) on commit delete rows;
create temp table temp_parted_oncommit_test2
  partition of temp_parted_oncommit_test
  for values in (2) on commit drop;
insert into temp_parted_oncommit_test values (1), (2);
commit;
-- no relations remain in this case.
select relname from pg_class where relname ~ '^temp_parted_oncommit_test';
 relname 
---------
(0 rows)

-- Using ON COMMIT DELETE on a partitioned table does not remove
-- all rows if partitions preserve their data.
begin;
create temp table temp_parted_oncommit_test (a int)
  partition by list (a) on commit delete rows;
create temp table temp_parted_oncommit_test1
  partition of temp_parted_oncommit_test
  for values in (1) on commit preserve rows;
create temp table temp_parted_oncommit_test2
  partition of temp_parted_oncommit_test
  for values in (2) on commit drop;
insert into temp_parted_oncommit_test values (1), (2);
commit;
-- Data from the remaining partition is still here as its rows are
-- preserved.
select * from temp_parted_oncommit_test;
 a 
---
 1
(1 row)

-- two relations remain in this case.
select relname from pg_class where relname ~ '^temp_parted_oncommit_test'
  order by relname;
          relname           
----------------------------
 temp_parted_oncommit_test
 temp_parted_oncommit_test1
(2 rows)

drop table temp_parted_oncommit_test;
-- Check dependencies between ON COMMIT actions with inheritance trees.
-- Using ON COMMIT DROP on a parent removes the whole set.
begin;
create temp table temp_inh_oncommit_test (a int) on commit drop;
create temp table temp_inh_oncommit_test1 ()
  inherits(temp_inh_oncommit_test) on commit delete rows;
insert into temp_inh_oncommit_test1 values (1);
commit;
-- no relations remain in this case
select relname from pg_class where relname ~ '^temp_inh_oncommit_test';
 relname 
---------
(0 rows)

-- Data on the parent is removed, and the child goes away.
begin;
create temp table temp_inh_oncommit_test (a int) on commit delete rows;
create temp table temp_inh_oncommit_test1 ()
  inherits(temp_inh_oncommit_test) on commit drop;
insert into temp_inh_oncommit_test1 values (1);
insert into temp_inh_oncommit_test values (1);
commit;
select * from temp_inh_oncommit_test;
 a 
---
(0 rows)

-- one relation remains
select relname from pg_class where relname ~ '^temp_inh_oncommit_test';
        relname         
------------------------
 temp_inh_oncommit_test
(1 row)

drop table temp_inh_oncommit_test;
-- Tests with two-phase commit
-- Transactions creating objects in a temporary namespace cannot be used
-- with two-phase commit.
-- These cases generate errors about temporary namespace.
-- Function creation
begin;
create function pg_temp.twophase_func() returns void as
  $$ select '2pc_func'::text $$ language sql;
prepare transaction 'twophase_func';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
-- Function drop
create function pg_temp.twophase_func() returns void as
  $$ select '2pc_func'::text $$ language sql;
begin;
drop function pg_temp.twophase_func();
prepare transaction 'twophase_func';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
-- Operator creation
begin;
create operator pg_temp.@@ (leftarg = int4, rightarg = int4, procedure = int4mi);
prepare transaction 'twophase_operator';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
-- These generate errors about temporary tables.
begin;
create type pg_temp.twophase_type as (a int);
prepare transaction 'twophase_type';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
begin;
create view pg_temp.twophase_view as select 1;
prepare transaction 'twophase_view';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
begin;
create sequence pg_temp.twophase_seq;
prepare transaction 'twophase_sequence';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
-- Temporary tables cannot be used with two-phase commit.
create temp table twophase_tab (a int);
begin;
select a from twophase_tab;
 a 
---
(0 rows)

prepare transaction 'twophase_tab';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
begin;
insert into twophase_tab values (1);
prepare transaction 'twophase_tab';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
begin;
lock twophase_tab in access exclusive mode;
prepare transaction 'twophase_tab';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
begin;
drop table twophase_tab;
prepare transaction 'twophase_tab';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects
-- Corner case: current_schema may create a temporary schema if namespace
-- creation is pending, so check after that.  First reset the connection
-- to remove the temporary namespace.
\c -
SET search_path TO 'pg_temp';
BEGIN;
SELECT current_schema() ~ 'pg_temp' AS is_temp_schema;
 is_temp_schema 
----------------
 t
(1 row)

PREPARE TRANSACTION 'twophase_search';
ERROR:  cannot PREPARE a transaction that has operated on temporary objects