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