src/test/regress/expected/plancache.out

   1 --
   2 -- Tests to exercise the plan caching/invalidation mechanism
   3 --
   4 CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl;
   5 -- create and use a cached plan
   6 PREPARE prepstmt AS SELECT * FROM pcachetest;
   7 EXECUTE 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
  18 PREPARE prepstmt2(bigint) AS SELECT * FROM pcachetest WHERE q1 = $1;
  19 EXECUTE prepstmt2(123);
  20  q1  |        q2
  21 -----+------------------
  22  123 |              456
  23  123 | 4567890123456789
  24 (2 rows)
  25
  26 -- invalidate the plans and see what happens
  27 DROP TABLE pcachetest;
  28 EXECUTE prepstmt;
  29 ERROR:  relation "pcachetest" does not exist
  30 EXECUTE prepstmt2(123);
  31 ERROR:  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)
  34 CREATE TEMP TABLE pcachetest AS SELECT * FROM int8_tbl ORDER BY 2;
  35 EXECUTE prepstmt;
  36         q1        |        q2
  37 ------------------+-------------------
  38  4567890123456789 | -4567890123456789
  39  4567890123456789 |               123
  40               123 |               456
  41               123 |  4567890123456789
  42  4567890123456789 |  4567890123456789
  43 (5 rows)
  44
  45 EXECUTE 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
  54 ALTER TABLE pcachetest ADD COLUMN q3 bigint;
  55 EXECUTE prepstmt;
  56 ERROR:  cached plan must not change result type
  57 EXECUTE prepstmt2(123);
  58 ERROR:  cached plan must not change result type
  59 -- but we're nice guys and will let you undo your mistake
  60 ALTER TABLE pcachetest DROP COLUMN q3;
  61 EXECUTE prepstmt;
  62         q1        |        q2
  63 ------------------+-------------------
  64  4567890123456789 | -4567890123456789
  65  4567890123456789 |               123
  66               123 |               456
  67               123 |  4567890123456789
  68  4567890123456789 |  4567890123456789
  69 (5 rows)
  70
  71 EXECUTE 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
  80 CREATE TEMP VIEW pcacheview AS
  81   SELECT * FROM pcachetest;
  82 PREPARE vprep AS SELECT * FROM pcacheview;
  83 EXECUTE vprep;
  84         q1        |        q2
  85 ------------------+-------------------
  86  4567890123456789 | -4567890123456789
  87  4567890123456789 |               123
  88               123 |               456
  89               123 |  4567890123456789
  90  4567890123456789 |  4567890123456789
  91 (5 rows)
  92
  93 CREATE OR REPLACE TEMP VIEW pcacheview AS
  94   SELECT q1, q2/2 AS q2 FROM pcachetest;
  95 EXECUTE 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
 106 create function cache_test(int) returns int as $$
 107 declare total int;
 108 begin
 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;
 117 end
 118 $$ language plpgsql;
 119 select cache_test(1);
 120  cache_test
 121 ------------
 122          37
 123 (1 row)
 124
 125 select cache_test(2);
 126  cache_test
 127 ------------
 128          38
 129 (1 row)
 130
 131 select cache_test(3);
 132  cache_test
 133 ------------
 134          39
 135 (1 row)
 136
 137 -- Check invalidation of plpgsql "simple expression"
 138 create temp view v1 as
 139   select 2+2 as f1;
 140 create function cache_test_2() returns int as $$
 141 begin
 142         return f1 from v1;
 143 end$$ language plpgsql;
 144 select cache_test_2();
 145  cache_test_2
 146 --------------
 147             4
 148 (1 row)
 149
 150 create or replace temp view v1 as
 151   select 2+2+4 as f1;
 152 select cache_test_2();
 153  cache_test_2
 154 --------------
 155             8
 156 (1 row)
 157
 158 create or replace temp view v1 as
 159   select 2+2+4+(select max(unique1) from tenk1) as f1;
 160 select 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
 167 create schema s1
 168   create table abc (f1 int);
 169 create schema s2
 170   create table abc (f1 int);
 171 insert into s1.abc values(123);
 172 insert into s2.abc values(456);
 173 set search_path = s1;
 174 prepare p1 as select f1 from abc;
 175 execute p1;
 176  f1
 177 -----
 178  123
 179 (1 row)
 180
 181 set search_path = s2;
 182 select f1 from abc;
 183  f1
 184 -----
 185  456
 186 (1 row)
 187
 188 execute p1;
 189  f1
 190 -----
 191  456
 192 (1 row)
 193
 194 alter table s1.abc add column f2 float8;   -- force replan
 195 execute p1;
 196  f1
 197 -----
 198  456
 199 (1 row)
 200
 201 drop schema s1 cascade;
 202 NOTICE:  drop cascades to table s1.abc
 203 drop schema s2 cascade;
 204 NOTICE:  drop cascades to table abc
 205 reset search_path;
 206 -- Check that invalidation deals with regclass constants
 207 create temp sequence seq;
 208 prepare p2 as select nextval('seq');
 209 execute p2;
 210  nextval
 211 ---------
 212        1
 213 (1 row)
 214
 215 drop sequence seq;
 216 create temp sequence seq;
 217 execute 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)
 225 create function cachebug() returns void as $$
 226 declare r int;
 227 begin
 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;
 234 end$$ language plpgsql;
 235 select cachebug();
 236 NOTICE:  table "temptable" does not exist, skipping
 237 NOTICE:  1
 238 NOTICE:  2
 239 NOTICE:  3
 240  cachebug
 241 ----------
 242
 243 (1 row)
 244
 245 select cachebug();
 246 NOTICE:  drop cascades to view vv
 247 NOTICE:  1
 248 NOTICE:  2
 249 NOTICE:  3
 250  cachebug
 251 ----------
 252
 253 (1 row)
 254
 255 -- Check that addition or removal of any partition is correctly dealt with by
 256 -- default partition table when it is being used in prepared statement.
 257 create table pc_list_parted (a int) partition by list(a);
 258 create table pc_list_part_null partition of pc_list_parted for values in (null);
 259 create table pc_list_part_1 partition of pc_list_parted for values in (1);
 260 create table pc_list_part_def partition of pc_list_parted default;
 261 prepare pstmt_def_insert (int) as insert into pc_list_part_def values($1);
 262 -- should fail
 263 execute pstmt_def_insert(null);
 264 ERROR:  new row for relation "pc_list_part_def" violates partition constraint
 265 DETAIL:  Failing row contains (null).
 266 execute pstmt_def_insert(1);
 267 ERROR:  new row for relation "pc_list_part_def" violates partition constraint
 268 DETAIL:  Failing row contains (1).
 269 create table pc_list_part_2 partition of pc_list_parted for values in (2);
 270 execute pstmt_def_insert(2);
 271 ERROR:  new row for relation "pc_list_part_def" violates partition constraint
 272 DETAIL:  Failing row contains (2).
 273 alter table pc_list_parted detach partition pc_list_part_null;
 274 -- should be ok
 275 execute pstmt_def_insert(null);
 276 drop table pc_list_part_1;
 277 -- should be ok
 278 execute pstmt_def_insert(1);
 279 drop table pc_list_parted, pc_list_part_null;
 280 deallocate pstmt_def_insert;
 281 -- Test plan_cache_mode
 282 create table test_mode (a int);
 283 insert into test_mode select 1 from generate_series(1,1000) union all select 2;
 284 create index on test_mode (a);
 285 analyze test_mode;
 286 prepare test_mode_pp (int) as select count(*) from test_mode where a = $1;
 287 select name, generic_plans, custom_plans from pg_prepared_statements
 288   where  name = 'test_mode_pp';
 289      name     | generic_plans | custom_plans
 290 --------------+---------------+--------------
 291  test_mode_pp |             0 |            0
 292 (1 row)
 293
 294 -- up to 5 executions, custom plan is used
 295 set plan_cache_mode to auto;
 296 explain (costs off) execute test_mode_pp(2);
 297                         QUERY PLAN
 298 ----------------------------------------------------------
 299  Aggregate
 300    ->  Index Only Scan using test_mode_a_idx on test_mode
 301          Index Cond: (a = 2)
 302 (3 rows)
 303
 304 select name, generic_plans, custom_plans from pg_prepared_statements
 305   where  name = 'test_mode_pp';
 306      name     | generic_plans | custom_plans
 307 --------------+---------------+--------------
 308  test_mode_pp |             0 |            1
 309 (1 row)
 310
 311 -- force generic plan
 312 set plan_cache_mode to force_generic_plan;
 313 explain (costs off) execute test_mode_pp(2);
 314          QUERY PLAN
 315 -----------------------------
 316  Aggregate
 317    ->  Seq Scan on test_mode
 318          Filter: (a = $1)
 319 (3 rows)
 320
 321 select name, generic_plans, custom_plans from pg_prepared_statements
 322   where  name = 'test_mode_pp';
 323      name     | generic_plans | custom_plans
 324 --------------+---------------+--------------
 325  test_mode_pp |             1 |            1
 326 (1 row)
 327
 328 -- get to generic plan by 5 executions
 329 set plan_cache_mode to auto;
 330 execute test_mode_pp(1); -- 1x
 331  count
 332 -------
 333   1000
 334 (1 row)
 335
 336 execute test_mode_pp(1); -- 2x
 337  count
 338 -------
 339   1000
 340 (1 row)
 341
 342 execute test_mode_pp(1); -- 3x
 343  count
 344 -------
 345   1000
 346 (1 row)
 347
 348 execute test_mode_pp(1); -- 4x
 349  count
 350 -------
 351   1000
 352 (1 row)
 353
 354 select name, generic_plans, custom_plans from pg_prepared_statements
 355   where  name = 'test_mode_pp';
 356      name     | generic_plans | custom_plans
 357 --------------+---------------+--------------
 358  test_mode_pp |             1 |            5
 359 (1 row)
 360
 361 execute test_mode_pp(1); -- 5x
 362  count
 363 -------
 364   1000
 365 (1 row)
 366
 367 select name, generic_plans, custom_plans from pg_prepared_statements
 368   where  name = 'test_mode_pp';
 369      name     | generic_plans | custom_plans
 370 --------------+---------------+--------------
 371  test_mode_pp |             2 |            5
 372 (1 row)
 373
 374 -- we should now get a really bad plan
 375 explain (costs off) execute test_mode_pp(2);
 376          QUERY PLAN
 377 -----------------------------
 378  Aggregate
 379    ->  Seq Scan on test_mode
 380          Filter: (a = $1)
 381 (3 rows)
 382
 383 -- but we can force a custom plan
 384 set plan_cache_mode to force_custom_plan;
 385 explain (costs off) execute test_mode_pp(2);
 386                         QUERY PLAN
 387 ----------------------------------------------------------
 388  Aggregate
 389    ->  Index Only Scan using test_mode_a_idx on test_mode
 390          Index Cond: (a = 2)
 391 (3 rows)
 392
 393 select name, generic_plans, custom_plans from pg_prepared_statements
 394   where  name = 'test_mode_pp';
 395      name     | generic_plans | custom_plans
 396 --------------+---------------+--------------
 397  test_mode_pp |             3 |            6
 398 (1 row)
 399
 400 drop table test_mode;