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