1-- 2-- Tests to exercise the plan caching/invalidation mechanism 3-- 4 5CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl; 6 7-- create and use a cached plan 8PREPARE prepstmt AS SELECT * FROM pcachetest; 9 10EXECUTE prepstmt; 11 12-- and one with parameters 13PREPARE prepstmt2(bigint) AS SELECT * FROM pcachetest WHERE q1 = $1; 14 15EXECUTE prepstmt2(123); 16 17-- invalidate the plans and see what happens 18DROP TABLE pcachetest; 19 20EXECUTE prepstmt; 21EXECUTE prepstmt2(123); 22 23-- recreate the temp table (this demonstrates that the raw plan is 24-- purely textual and doesn't depend on OIDs, for instance) 25CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl ORDER BY 2; 26 27EXECUTE prepstmt; 28EXECUTE prepstmt2(123); 29 30-- prepared statements should prevent change in output tupdesc, 31-- since clients probably aren't expecting that to change on the fly 32ALTER TABLE pcachetest ADD COLUMN q3 bigint; 33 34EXECUTE prepstmt; 35EXECUTE prepstmt2(123); 36 37-- but we're nice guys and will let you undo your mistake 38ALTER TABLE pcachetest DROP COLUMN q3; 39 40EXECUTE prepstmt; 41EXECUTE prepstmt2(123); 42 43-- Try it with a view, which isn't directly used in the resulting plan 44-- but should trigger invalidation anyway 45CREATE TEMP VIEW pcacheview AS 46 SELECT * FROM pcachetest; 47 48PREPARE vprep AS SELECT * FROM pcacheview; 49 50EXECUTE vprep; 51 52CREATE OR REPLACE TEMP VIEW pcacheview AS 53 SELECT q1, q2/2 AS q2 FROM pcachetest; 54 55EXECUTE vprep; 56 57-- Check basic SPI plan invalidation 58 59create function cache_test(int) returns int as $$ 60declare total int; 61begin 62 create temp table t1(f1 int); 63 insert into t1 values($1); 64 insert into t1 values(11); 65 insert into t1 values(12); 66 insert into t1 values(13); 67 select sum(f1) into total from t1; 68 drop table t1; 69 return total; 70end 71$$ language plpgsql; 72 73select cache_test(1); 74select cache_test(2); 75select cache_test(3); 76 77-- Check invalidation of plpgsql "simple expression" 78 79create temp view v1 as 80 select 2+2 as f1; 81 82create function cache_test_2() returns int as $$ 83begin 84 return f1 from v1; 85end$$ language plpgsql; 86 87select cache_test_2(); 88 89create or replace temp view v1 as 90 select 2+2+4 as f1; 91select cache_test_2(); 92 93create or replace temp view v1 as 94 select 2+2+4+(select max(unique1) from tenk1) as f1; 95select cache_test_2(); 96 97--- Check that change of search_path is honored when re-using cached plan 98 99create schema s1 100 create table abc (f1 int); 101 102create schema s2 103 create table abc (f1 int); 104 105insert into s1.abc values(123); 106insert into s2.abc values(456); 107 108set search_path = s1; 109 110prepare p1 as select f1 from abc; 111 112execute p1; 113 114set search_path = s2; 115 116select f1 from abc; 117 118execute p1; 119 120alter table s1.abc add column f2 float8; -- force replan 121 122execute p1; 123 124drop schema s1 cascade; 125drop schema s2 cascade; 126 127reset search_path; 128 129-- Check that invalidation deals with regclass constants 130 131create temp sequence seq; 132 133prepare p2 as select nextval('seq'); 134 135execute p2; 136 137drop sequence seq; 138 139create temp sequence seq; 140 141execute p2; 142 143-- Check DDL via SPI, immediately followed by SPI plan re-use 144-- (bug in original coding) 145 146create function cachebug() returns void as $$ 147declare r int; 148begin 149 drop table if exists temptable cascade; 150 create temp table temptable as select * from generate_series(1,3) as f1; 151 create temp view vv as select * from temptable; 152 for r in select * from vv loop 153 raise notice '%', r; 154 end loop; 155end$$ language plpgsql; 156 157select cachebug(); 158select cachebug(); 159 160-- Check that addition or removal of any partition is correctly dealt with by 161-- default partition table when it is being used in prepared statement. 162create table pc_list_parted (a int) partition by list(a); 163create table pc_list_part_null partition of pc_list_parted for values in (null); 164create table pc_list_part_1 partition of pc_list_parted for values in (1); 165create table pc_list_part_def partition of pc_list_parted default; 166prepare pstmt_def_insert (int) as insert into pc_list_part_def values($1); 167-- should fail 168execute pstmt_def_insert(null); 169execute pstmt_def_insert(1); 170create table pc_list_part_2 partition of pc_list_parted for values in (2); 171execute pstmt_def_insert(2); 172alter table pc_list_parted detach partition pc_list_part_null; 173-- should be ok 174execute pstmt_def_insert(null); 175drop table pc_list_part_1; 176-- should be ok 177execute pstmt_def_insert(1); 178drop table pc_list_parted, pc_list_part_null; 179deallocate pstmt_def_insert; 180