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();