1-- 2-- TEMP 3-- Test temp relations and indexes 4-- 5-- test temp table/index masking 6CREATE TABLE temptest(col int); 7CREATE INDEX i_temptest ON temptest(col); 8CREATE TEMP TABLE temptest(tcol int); 9CREATE INDEX i_temptest ON temptest(tcol); 10SELECT * FROM temptest; 11 tcol 12------ 13(0 rows) 14 15DROP INDEX i_temptest; 16DROP TABLE temptest; 17SELECT * FROM temptest; 18 col 19----- 20(0 rows) 21 22DROP INDEX i_temptest; 23DROP TABLE temptest; 24-- test temp table selects 25CREATE TABLE temptest(col int); 26INSERT INTO temptest VALUES (1); 27CREATE TEMP TABLE temptest(tcol float); 28INSERT INTO temptest VALUES (2.1); 29SELECT * FROM temptest; 30 tcol 31------ 32 2.1 33(1 row) 34 35DROP TABLE temptest; 36SELECT * FROM temptest; 37 col 38----- 39 1 40(1 row) 41 42DROP TABLE temptest; 43-- test temp table deletion 44CREATE TEMP TABLE temptest(col int); 45\c 46SELECT * FROM temptest; 47ERROR: relation "temptest" does not exist 48LINE 1: SELECT * FROM temptest; 49 ^ 50-- Test ON COMMIT DELETE ROWS 51CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS; 52-- while we're here, verify successful truncation of index with SQL function 53CREATE INDEX ON temptest(bit_length('')); 54BEGIN; 55INSERT INTO temptest VALUES (1); 56INSERT INTO temptest VALUES (2); 57SELECT * FROM temptest; 58 col 59----- 60 1 61 2 62(2 rows) 63 64COMMIT; 65SELECT * FROM temptest; 66 col 67----- 68(0 rows) 69 70DROP TABLE temptest; 71BEGIN; 72CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; 73SELECT * FROM temptest; 74 col 75----- 76 1 77(1 row) 78 79COMMIT; 80SELECT * FROM temptest; 81 col 82----- 83(0 rows) 84 85DROP TABLE temptest; 86-- Test ON COMMIT DROP 87BEGIN; 88CREATE TEMP TABLE temptest(col int) ON COMMIT DROP; 89INSERT INTO temptest VALUES (1); 90INSERT INTO temptest VALUES (2); 91SELECT * FROM temptest; 92 col 93----- 94 1 95 2 96(2 rows) 97 98COMMIT; 99SELECT * FROM temptest; 100ERROR: relation "temptest" does not exist 101LINE 1: SELECT * FROM temptest; 102 ^ 103BEGIN; 104CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1; 105SELECT * FROM temptest; 106 col 107----- 108 1 109(1 row) 110 111COMMIT; 112SELECT * FROM temptest; 113ERROR: relation "temptest" does not exist 114LINE 1: SELECT * FROM temptest; 115 ^ 116-- ON COMMIT is only allowed for TEMP 117CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS; 118ERROR: ON COMMIT can only be used on temporary tables 119CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; 120ERROR: ON COMMIT can only be used on temporary tables 121-- Test foreign keys 122BEGIN; 123CREATE TEMP TABLE temptest1(col int PRIMARY KEY); 124CREATE TEMP TABLE temptest2(col int REFERENCES temptest1) 125 ON COMMIT DELETE ROWS; 126INSERT INTO temptest1 VALUES (1); 127INSERT INTO temptest2 VALUES (1); 128COMMIT; 129SELECT * FROM temptest1; 130 col 131----- 132 1 133(1 row) 134 135SELECT * FROM temptest2; 136 col 137----- 138(0 rows) 139 140BEGIN; 141CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; 142CREATE TEMP TABLE temptest4(col int REFERENCES temptest3); 143COMMIT; 144ERROR: unsupported ON COMMIT and foreign key combination 145DETAIL: Table "temptest4" references "temptest3", but they do not have the same ON COMMIT setting. 146-- Test manipulation of temp schema's placement in search path 147create table public.whereami (f1 text); 148insert into public.whereami values ('public'); 149create temp table whereami (f1 text); 150insert into whereami values ('temp'); 151create function public.whoami() returns text 152 as $$select 'public'::text$$ language sql; 153create function pg_temp.whoami() returns text 154 as $$select 'temp'::text$$ language sql; 155-- default should have pg_temp implicitly first, but only for tables 156select * from whereami; 157 f1 158------ 159 temp 160(1 row) 161 162select whoami(); 163 whoami 164-------- 165 public 166(1 row) 167 168-- can list temp first explicitly, but it still doesn't affect functions 169set search_path = pg_temp, public; 170select * from whereami; 171 f1 172------ 173 temp 174(1 row) 175 176select whoami(); 177 whoami 178-------- 179 public 180(1 row) 181 182-- or put it last for security 183set search_path = public, pg_temp; 184select * from whereami; 185 f1 186-------- 187 public 188(1 row) 189 190select whoami(); 191 whoami 192-------- 193 public 194(1 row) 195 196-- you can invoke a temp function explicitly, though 197select pg_temp.whoami(); 198 whoami 199-------- 200 temp 201(1 row) 202 203drop table public.whereami; 204-- types in temp schema 205set search_path = pg_temp, public; 206create domain pg_temp.nonempty as text check (value <> ''); 207-- function-syntax invocation of types matches rules for functions 208select nonempty(''); 209ERROR: function nonempty(unknown) does not exist 210LINE 1: select nonempty(''); 211 ^ 212HINT: No function matches the given name and argument types. You might need to add explicit type casts. 213select pg_temp.nonempty(''); 214ERROR: value for domain nonempty violates check constraint "nonempty_check" 215-- other syntax matches rules for tables 216select ''::nonempty; 217ERROR: value for domain nonempty violates check constraint "nonempty_check" 218reset search_path; 219-- For partitioned temp tables, ON COMMIT actions ignore storage-less 220-- partitioned tables. 221begin; 222create temp table temp_parted_oncommit (a int) 223 partition by list (a) on commit delete rows; 224create temp table temp_parted_oncommit_1 225 partition of temp_parted_oncommit 226 for values in (1) on commit delete rows; 227insert into temp_parted_oncommit values (1); 228commit; 229-- partitions are emptied by the previous commit 230select * from temp_parted_oncommit; 231 a 232--- 233(0 rows) 234 235drop table temp_parted_oncommit; 236-- Check dependencies between ON COMMIT actions with a partitioned 237-- table and its partitions. Using ON COMMIT DROP on a parent removes 238-- the whole set. 239begin; 240create temp table temp_parted_oncommit_test (a int) 241 partition by list (a) on commit drop; 242create temp table temp_parted_oncommit_test1 243 partition of temp_parted_oncommit_test 244 for values in (1) on commit delete rows; 245create temp table temp_parted_oncommit_test2 246 partition of temp_parted_oncommit_test 247 for values in (2) on commit drop; 248insert into temp_parted_oncommit_test values (1), (2); 249commit; 250-- no relations remain in this case. 251select relname from pg_class where relname ~ '^temp_parted_oncommit_test'; 252 relname 253--------- 254(0 rows) 255 256-- Using ON COMMIT DELETE on a partitioned table does not remove 257-- all rows if partitions preserve their data. 258begin; 259create temp table temp_parted_oncommit_test (a int) 260 partition by list (a) on commit delete rows; 261create temp table temp_parted_oncommit_test1 262 partition of temp_parted_oncommit_test 263 for values in (1) on commit preserve rows; 264create temp table temp_parted_oncommit_test2 265 partition of temp_parted_oncommit_test 266 for values in (2) on commit drop; 267insert into temp_parted_oncommit_test values (1), (2); 268commit; 269-- Data from the remaining partition is still here as its rows are 270-- preserved. 271select * from temp_parted_oncommit_test; 272 a 273--- 274 1 275(1 row) 276 277-- two relations remain in this case. 278select relname from pg_class where relname ~ '^temp_parted_oncommit_test' 279 order by relname; 280 relname 281---------------------------- 282 temp_parted_oncommit_test 283 temp_parted_oncommit_test1 284(2 rows) 285 286drop table temp_parted_oncommit_test; 287-- Check dependencies between ON COMMIT actions with inheritance trees. 288-- Using ON COMMIT DROP on a parent removes the whole set. 289begin; 290create temp table temp_inh_oncommit_test (a int) on commit drop; 291create temp table temp_inh_oncommit_test1 () 292 inherits(temp_inh_oncommit_test) on commit delete rows; 293insert into temp_inh_oncommit_test1 values (1); 294commit; 295-- no relations remain in this case 296select relname from pg_class where relname ~ '^temp_inh_oncommit_test'; 297 relname 298--------- 299(0 rows) 300 301-- Data on the parent is removed, and the child goes away. 302begin; 303create temp table temp_inh_oncommit_test (a int) on commit delete rows; 304create temp table temp_inh_oncommit_test1 () 305 inherits(temp_inh_oncommit_test) on commit drop; 306insert into temp_inh_oncommit_test1 values (1); 307insert into temp_inh_oncommit_test values (1); 308commit; 309select * from temp_inh_oncommit_test; 310 a 311--- 312(0 rows) 313 314-- one relation remains 315select relname from pg_class where relname ~ '^temp_inh_oncommit_test'; 316 relname 317------------------------ 318 temp_inh_oncommit_test 319(1 row) 320 321drop table temp_inh_oncommit_test; 322-- Tests with two-phase commit 323-- Transactions creating objects in a temporary namespace cannot be used 324-- with two-phase commit. 325-- These cases generate errors about temporary namespace. 326-- Function creation 327begin; 328create function pg_temp.twophase_func() returns void as 329 $$ select '2pc_func'::text $$ language sql; 330prepare transaction 'twophase_func'; 331ERROR: cannot PREPARE a transaction that has operated on temporary objects 332-- Function drop 333create function pg_temp.twophase_func() returns void as 334 $$ select '2pc_func'::text $$ language sql; 335begin; 336drop function pg_temp.twophase_func(); 337prepare transaction 'twophase_func'; 338ERROR: cannot PREPARE a transaction that has operated on temporary objects 339-- Operator creation 340begin; 341create operator pg_temp.@@ (leftarg = int4, rightarg = int4, procedure = int4mi); 342prepare transaction 'twophase_operator'; 343ERROR: cannot PREPARE a transaction that has operated on temporary objects 344-- These generate errors about temporary tables. 345begin; 346create type pg_temp.twophase_type as (a int); 347prepare transaction 'twophase_type'; 348ERROR: cannot PREPARE a transaction that has operated on temporary tables 349begin; 350create view pg_temp.twophase_view as select 1; 351prepare transaction 'twophase_view'; 352ERROR: cannot PREPARE a transaction that has operated on temporary tables 353begin; 354create sequence pg_temp.twophase_seq; 355prepare transaction 'twophase_sequence'; 356ERROR: cannot PREPARE a transaction that has operated on temporary tables 357-- Temporary tables cannot be used with two-phase commit. 358create temp table twophase_tab (a int); 359begin; 360select a from twophase_tab; 361 a 362--- 363(0 rows) 364 365prepare transaction 'twophase_tab'; 366ERROR: cannot PREPARE a transaction that has operated on temporary tables 367begin; 368insert into twophase_tab values (1); 369prepare transaction 'twophase_tab'; 370ERROR: cannot PREPARE a transaction that has operated on temporary tables 371begin; 372lock twophase_tab in access exclusive mode; 373prepare transaction 'twophase_tab'; 374ERROR: cannot PREPARE a transaction that has operated on temporary tables 375begin; 376drop table twophase_tab; 377prepare transaction 'twophase_tab'; 378ERROR: cannot PREPARE a transaction that has operated on temporary tables 379-- Corner case: current_schema may create a temporary schema if namespace 380-- creation is pending, so check after that. First reset the connection 381-- to remove the temporary namespace, and make sure that non-parallel plans 382-- are used. 383\c - 384SET max_parallel_workers = 0; 385SET max_parallel_workers_per_gather = 0; 386SET search_path TO 'pg_temp'; 387BEGIN; 388SELECT current_schema() ~ 'pg_temp' AS is_temp_schema; 389 is_temp_schema 390---------------- 391 t 392(1 row) 393 394PREPARE TRANSACTION 'twophase_search'; 395ERROR: cannot PREPARE a transaction that has operated on temporary objects 396