1-- 2-- Tests to exercise the plan caching/invalidation mechanism 3-- 4CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl; 5-- create and use a cached plan 6PREPARE prepstmt AS SELECT * FROM pcachetest; 7EXECUTE prepstmt; 8 q1 | q2 9------------------+------------------- 10 123 | 456 11 123 | 4567890123456789 12 4567890123456789 | 123 13 4567890123456789 | 4567890123456789 14 4567890123456789 | -4567890123456789 15(5 rows) 16 17-- and one with parameters 18PREPARE prepstmt2(bigint) AS SELECT * FROM pcachetest WHERE q1 = $1; 19EXECUTE prepstmt2(123); 20 q1 | q2 21-----+------------------ 22 123 | 456 23 123 | 4567890123456789 24(2 rows) 25 26-- invalidate the plans and see what happens 27DROP TABLE pcachetest; 28EXECUTE prepstmt; 29ERROR: relation "pcachetest" does not exist 30EXECUTE prepstmt2(123); 31ERROR: relation "pcachetest" does not exist 32-- recreate the temp table (this demonstrates that the raw plan is 33-- purely textual and doesn't depend on OIDs, for instance) 34CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl ORDER BY 2; 35EXECUTE prepstmt; 36 q1 | q2 37------------------+------------------- 38 4567890123456789 | -4567890123456789 39 4567890123456789 | 123 40 123 | 456 41 123 | 4567890123456789 42 4567890123456789 | 4567890123456789 43(5 rows) 44 45EXECUTE prepstmt2(123); 46 q1 | q2 47-----+------------------ 48 123 | 456 49 123 | 4567890123456789 50(2 rows) 51 52-- prepared statements should prevent change in output tupdesc, 53-- since clients probably aren't expecting that to change on the fly 54ALTER TABLE pcachetest ADD COLUMN q3 bigint; 55EXECUTE prepstmt; 56ERROR: cached plan must not change result type 57EXECUTE prepstmt2(123); 58ERROR: cached plan must not change result type 59-- but we're nice guys and will let you undo your mistake 60ALTER TABLE pcachetest DROP COLUMN q3; 61EXECUTE prepstmt; 62 q1 | q2 63------------------+------------------- 64 4567890123456789 | -4567890123456789 65 4567890123456789 | 123 66 123 | 456 67 123 | 4567890123456789 68 4567890123456789 | 4567890123456789 69(5 rows) 70 71EXECUTE prepstmt2(123); 72 q1 | q2 73-----+------------------ 74 123 | 456 75 123 | 4567890123456789 76(2 rows) 77 78-- Try it with a view, which isn't directly used in the resulting plan 79-- but should trigger invalidation anyway 80CREATE TEMP VIEW pcacheview AS 81 SELECT * FROM pcachetest; 82PREPARE vprep AS SELECT * FROM pcacheview; 83EXECUTE vprep; 84 q1 | q2 85------------------+------------------- 86 4567890123456789 | -4567890123456789 87 4567890123456789 | 123 88 123 | 456 89 123 | 4567890123456789 90 4567890123456789 | 4567890123456789 91(5 rows) 92 93CREATE OR REPLACE TEMP VIEW pcacheview AS 94 SELECT q1, q2/2 AS q2 FROM pcachetest; 95EXECUTE vprep; 96 q1 | q2 97------------------+------------------- 98 4567890123456789 | -2283945061728394 99 4567890123456789 | 61 100 123 | 228 101 123 | 2283945061728394 102 4567890123456789 | 2283945061728394 103(5 rows) 104 105-- Check basic SPI plan invalidation 106create function cache_test(int) returns int as $$ 107declare total int; 108begin 109 create temp table t1(f1 int); 110 insert into t1 values($1); 111 insert into t1 values(11); 112 insert into t1 values(12); 113 insert into t1 values(13); 114 select sum(f1) into total from t1; 115 drop table t1; 116 return total; 117end 118$$ language plpgsql; 119select cache_test(1); 120 cache_test 121------------ 122 37 123(1 row) 124 125select cache_test(2); 126 cache_test 127------------ 128 38 129(1 row) 130 131select cache_test(3); 132 cache_test 133------------ 134 39 135(1 row) 136 137-- Check invalidation of plpgsql "simple expression" 138create temp view v1 as 139 select 2+2 as f1; 140create function cache_test_2() returns int as $$ 141begin 142 return f1 from v1; 143end$$ language plpgsql; 144select cache_test_2(); 145 cache_test_2 146-------------- 147 4 148(1 row) 149 150create or replace temp view v1 as 151 select 2+2+4 as f1; 152select cache_test_2(); 153 cache_test_2 154-------------- 155 8 156(1 row) 157 158create or replace temp view v1 as 159 select 2+2+4+(select max(unique1) from tenk1) as f1; 160select cache_test_2(); 161 cache_test_2 162-------------- 163 10007 164(1 row) 165 166--- Check that change of search_path is honored when re-using cached plan 167create schema s1 168 create table abc (f1 int); 169create schema s2 170 create table abc (f1 int); 171insert into s1.abc values(123); 172insert into s2.abc values(456); 173set search_path = s1; 174prepare p1 as select f1 from abc; 175execute p1; 176 f1 177----- 178 123 179(1 row) 180 181set search_path = s2; 182select f1 from abc; 183 f1 184----- 185 456 186(1 row) 187 188execute p1; 189 f1 190----- 191 456 192(1 row) 193 194alter table s1.abc add column f2 float8; -- force replan 195execute p1; 196 f1 197----- 198 456 199(1 row) 200 201drop schema s1 cascade; 202NOTICE: drop cascades to table s1.abc 203drop schema s2 cascade; 204NOTICE: drop cascades to table abc 205reset search_path; 206-- Check that invalidation deals with regclass constants 207create temp sequence seq; 208prepare p2 as select nextval('seq'); 209execute p2; 210 nextval 211--------- 212 1 213(1 row) 214 215drop sequence seq; 216create temp sequence seq; 217execute p2; 218 nextval 219--------- 220 1 221(1 row) 222 223-- Check DDL via SPI, immediately followed by SPI plan re-use 224-- (bug in original coding) 225create function cachebug() returns void as $$ 226declare r int; 227begin 228 drop table if exists temptable cascade; 229 create temp table temptable as select * from generate_series(1,3) as f1; 230 create temp view vv as select * from temptable; 231 for r in select * from vv loop 232 raise notice '%', r; 233 end loop; 234end$$ language plpgsql; 235select cachebug(); 236NOTICE: table "temptable" does not exist, skipping 237NOTICE: 1 238NOTICE: 2 239NOTICE: 3 240 cachebug 241---------- 242 243(1 row) 244 245select cachebug(); 246NOTICE: drop cascades to view vv 247NOTICE: 1 248NOTICE: 2 249NOTICE: 3 250 cachebug 251---------- 252 253(1 row) 254 255