blob: c38a06f1231f598e350886214a25b124cb4dfb80 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
--
-- Tests to exercise the plan caching/invalidation mechanism
--
CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl;
-- create and use a cached plan
PREPARE prepstmt AS SELECT * FROM pcachetest;
EXECUTE prepstmt;
q1 | q2
------------------+-------------------
123 | 456
123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(5 rows)
-- and one with parameters
PREPARE prepstmt2(bigint) AS SELECT * FROM pcachetest WHERE q1 = $1;
EXECUTE prepstmt2(123);
q1 | q2
-----+------------------
123 | 456
123 | 4567890123456789
(2 rows)
-- invalidate the plans and see what happens
DROP TABLE pcachetest;
EXECUTE prepstmt;
ERROR: relation "pcachetest" does not exist
EXECUTE prepstmt2(123);
ERROR: relation "pcachetest" does not exist
-- recreate the temp table (this demonstrates that the raw plan is
-- purely textual and doesn't depend on OIDs, for instance)
CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl ORDER BY 2;
EXECUTE prepstmt;
q1 | q2
------------------+-------------------
4567890123456789 | -4567890123456789
4567890123456789 | 123
123 | 456
123 | 4567890123456789
4567890123456789 | 4567890123456789
(5 rows)
EXECUTE prepstmt2(123);
q1 | q2
-----+------------------
123 | 456
123 | 4567890123456789
(2 rows)
-- prepared statements should prevent change in output tupdesc,
-- since clients probably aren't expecting that to change on the fly
ALTER TABLE pcachetest ADD COLUMN q3 bigint;
EXECUTE prepstmt;
ERROR: cached plan must not change result type
EXECUTE prepstmt2(123);
ERROR: cached plan must not change result type
-- but we're nice guys and will let you undo your mistake
ALTER TABLE pcachetest DROP COLUMN q3;
EXECUTE prepstmt;
q1 | q2
------------------+-------------------
4567890123456789 | -4567890123456789
4567890123456789 | 123
123 | 456
123 | 4567890123456789
4567890123456789 | 4567890123456789
(5 rows)
EXECUTE prepstmt2(123);
q1 | q2
-----+------------------
123 | 456
123 | 4567890123456789
(2 rows)
-- Try it with a view, which isn't directly used in the resulting plan
-- but should trigger invalidation anyway
CREATE TEMP VIEW pcacheview AS
SELECT * FROM pcachetest;
PREPARE vprep AS SELECT * FROM pcacheview;
EXECUTE vprep;
q1 | q2
------------------+-------------------
4567890123456789 | -4567890123456789
4567890123456789 | 123
123 | 456
123 | 4567890123456789
4567890123456789 | 4567890123456789
(5 rows)
CREATE OR REPLACE TEMP VIEW pcacheview AS
SELECT q1, q2/2 AS q2 FROM pcachetest;
EXECUTE vprep;
q1 | q2
------------------+-------------------
4567890123456789 | -2283945061728394
4567890123456789 | 61
123 | 228
123 | 2283945061728394
4567890123456789 | 2283945061728394
(5 rows)
-- Check basic SPI plan invalidation
create function cache_test(int) returns int as $$
declare total int;
begin
create temp table t1(f1 int);
insert into t1 values($1);
insert into t1 values(11);
insert into t1 values(12);
insert into t1 values(13);
select sum(f1) into total from t1;
drop table t1;
return total;
end
$$ language plpgsql;
select cache_test(1);
cache_test
------------
37
(1 row)
select cache_test(2);
cache_test
------------
38
(1 row)
select cache_test(3);
cache_test
------------
39
(1 row)
-- Check invalidation of plpgsql "simple expression"
create temp view v1 as
select 2+2 as f1;
create function cache_test_2() returns int as $$
begin
return f1 from v1;
end$$ language plpgsql;
select cache_test_2();
cache_test_2
--------------
4
(1 row)
create or replace temp view v1 as
select 2+2+4 as f1;
select cache_test_2();
cache_test_2
--------------
8
(1 row)
create or replace temp view v1 as
select 2+2+4+(select max(unique1) from tenk1) as f1;
select cache_test_2();
cache_test_2
--------------
10007
(1 row)
|
