2 -- Tests to exercise the plan caching/invalidation mechanism
5 CREATE TEMP TABLE foo AS SELECT * FROM int8_tbl;
7 -- create and use a cached plan
8 PREPARE prepstmt AS SELECT * FROM foo;
12 -- and one with parameters
13 PREPARE prepstmt2(bigint) AS SELECT * FROM foo WHERE q1 = $1;
15 EXECUTE prepstmt2(123);
17 -- invalidate the plans and see what happens
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 foo 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 foo ADD COLUMN q3 bigint;
35 EXECUTE prepstmt2(123);
37 -- but we're nice guys and will let you undo your mistake
38 ALTER TABLE foo 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 voo AS SELECT * FROM foo;
47 PREPARE vprep AS SELECT * FROM voo;
51 CREATE OR REPLACE TEMP VIEW voo AS SELECT q1, q2/2 AS q2 FROM foo;
55 -- Check basic SPI plan invalidation
57 create function cache_test(int) returns int as $$
60 create temp table t1(f1 int);
61 insert into t1 values($1);
62 insert into t1 values(11);
63 insert into t1 values(12);
64 insert into t1 values(13);
65 select sum(f1) into total from t1;
75 -- Check invalidation of plpgsql "simple expression"
77 create temp view v1 as
80 create function cache_test_2() returns int as $$
83 end$$ language plpgsql;
85 select cache_test_2();
87 create or replace temp view v1 as
89 select cache_test_2();
91 create or replace temp view v1 as
92 select 2+2+4+(select max(unique1) from tenk1) as f1;
93 select cache_test_2();