2 -- Tests to exercise the plan caching/invalidation mechanism
5 CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl;
7 -- create and use a cached plan
8 PREPARE prepstmt AS SELECT * FROM pcachetest;
12 -- and one with parameters
13 PREPARE prepstmt2(bigint) AS SELECT * FROM pcachetest WHERE q1 = $1;
15 EXECUTE prepstmt2(123);
17 -- invalidate the plans and see what happens
18 DROP TABLE pcachetest;
21 EXECUTE prepstmt2(123);
23 -- recreate the temp table (this demonstrates that the raw plan is
24 -- purely textual and doesn't depend on OIDs, for instance)
25 CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl ORDER BY 2;
28 EXECUTE prepstmt2(123);
30 -- prepared statements should prevent change in output tupdesc,
31 -- since clients probably aren't expecting that to change on the fly
32 ALTER TABLE pcachetest ADD COLUMN q3 bigint;
35 EXECUTE prepstmt2(123);
37 -- but we're nice guys and will let you undo your mistake
38 ALTER TABLE pcachetest DROP COLUMN q3;
41 EXECUTE prepstmt2(123);
43 -- Try it with a view, which isn't directly used in the resulting plan
44 -- but should trigger invalidation anyway
45 CREATE TEMP VIEW pcacheview AS
46 SELECT * FROM pcachetest;
48 PREPARE vprep AS SELECT * FROM pcacheview;
52 CREATE OR REPLACE TEMP VIEW pcacheview AS
53 SELECT q1, q2/2 AS q2 FROM pcachetest;
57 -- Check basic SPI plan invalidation
59 create function cache_test(int) returns int as $$
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;
77 -- Check invalidation of plpgsql "simple expression"
79 create temp view v1 as
82 create function cache_test_2() returns int as $$
85 end$$ language plpgsql;
87 select cache_test_2();
89 create or replace temp view v1 as
91 select cache_test_2();
93 create or replace temp view v1 as
94 select 2+2+4+(select max(unique1) from tenk1) as f1;
95 select cache_test_2();
97 --- Check that change of search_path is honored when re-using cached plan
100 create table abc (f1 int);
103 create table abc (f1 int);
105 insert into s1.abc values(123);
106 insert into s2.abc values(456);
108 set search_path = s1;
110 prepare p1 as select f1 from abc;
114 set search_path = s2;
120 alter table s1.abc add column f2 float8; -- force replan
124 drop schema s1 cascade;
125 drop schema s2 cascade;
129 -- Check that invalidation deals with regclass constants
131 create temp sequence seq;
133 prepare p2 as select nextval('seq');
139 create temp sequence seq;
143 -- Check DDL via SPI, immediately followed by SPI plan re-use
144 -- (bug in original coding)
146 create function cachebug() returns void as $$
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
155 end$$ language plpgsql;