1 files changed, 400 insertions, 0 deletions
diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out
new file mode 100644
index 0000000..4e59188
--- /dev/null
+++ b/src/test/regress/expected/plancache.out
@@ -0,0 +1,400 @@
+--
+-- 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)
+
+--- Check that change of search_path is honored when re-using cached plan
+create schema s1
+  create table abc (f1 int);
+create schema s2
+  create table abc (f1 int);
+insert into s1.abc values(123);
+insert into s2.abc values(456);
+set search_path = s1;
+prepare p1 as select f1 from abc;
+execute p1;
+ f1  
+-----
+ 123
+(1 row)
+
+set search_path = s2;
+select f1 from abc;
+ f1  
+-----
+ 456
+(1 row)
+
+execute p1;
+ f1  
+-----
+ 456
+(1 row)
+
+alter table s1.abc add column f2 float8;   -- force replan
+execute p1;
+ f1  
+-----
+ 456
+(1 row)
+
+drop schema s1 cascade;
+NOTICE:  drop cascades to table s1.abc
+drop schema s2 cascade;
+NOTICE:  drop cascades to table abc
+reset search_path;
+-- Check that invalidation deals with regclass constants
+create temp sequence seq;
+prepare p2 as select nextval('seq');
+execute p2;
+ nextval 
+---------
+       1
+(1 row)
+
+drop sequence seq;
+create temp sequence seq;
+execute p2;
+ nextval 
+---------
+       1
+(1 row)
+
+-- Check DDL via SPI, immediately followed by SPI plan re-use
+-- (bug in original coding)
+create function cachebug() returns void as $$
+declare r int;
+begin
+  drop table if exists temptable cascade;
+  create temp table temptable as select * from generate_series(1,3) as f1;
+  create temp view vv as select * from temptable;
+  for r in select * from vv loop
+    raise notice '%', r;
+  end loop;
+end$$ language plpgsql;
+select cachebug();
+NOTICE:  table "temptable" does not exist, skipping
+NOTICE:  1
+NOTICE:  2
+NOTICE:  3
+ cachebug 
+----------
+ 
+(1 row)
+
+select cachebug();
+NOTICE:  drop cascades to view vv
+NOTICE:  1
+NOTICE:  2
+NOTICE:  3
+ cachebug 
+----------
+ 
+(1 row)
+
+-- Check that addition or removal of any partition is correctly dealt with by
+-- default partition table when it is being used in prepared statement.
+create table pc_list_parted (a int) partition by list(a);
+create table pc_list_part_null partition of pc_list_parted for values in (null);
+create table pc_list_part_1 partition of pc_list_parted for values in (1);
+create table pc_list_part_def partition of pc_list_parted default;
+prepare pstmt_def_insert (int) as insert into pc_list_part_def values($1);
+-- should fail
+execute pstmt_def_insert(null);
+ERROR:  new row for relation "pc_list_part_def" violates partition constraint
+DETAIL:  Failing row contains (null).
+execute pstmt_def_insert(1);
+ERROR:  new row for relation "pc_list_part_def" violates partition constraint
+DETAIL:  Failing row contains (1).
+create table pc_list_part_2 partition of pc_list_parted for values in (2);
+execute pstmt_def_insert(2);
+ERROR:  new row for relation "pc_list_part_def" violates partition constraint
+DETAIL:  Failing row contains (2).
+alter table pc_list_parted detach partition pc_list_part_null;
+-- should be ok
+execute pstmt_def_insert(null);
+drop table pc_list_part_1;
+-- should be ok
+execute pstmt_def_insert(1);
+drop table pc_list_parted, pc_list_part_null;
+deallocate pstmt_def_insert;
+-- Test plan_cache_mode
+create table test_mode (a int);
+insert into test_mode select 1 from generate_series(1,1000) union all select 2;
+create index on test_mode (a);
+analyze test_mode;
+prepare test_mode_pp (int) as select count(*) from test_mode where a = $1;
+select name, generic_plans, custom_plans from pg_prepared_statements
+  where  name = 'test_mode_pp';
+     name     | generic_plans | custom_plans 
+--------------+---------------+--------------
+ test_mode_pp |             0 |            0
+(1 row)
+
+-- up to 5 executions, custom plan is used
+set plan_cache_mode to auto;
+explain (costs off) execute test_mode_pp(2);
+                        QUERY PLAN                        
+----------------------------------------------------------
+ Aggregate
+   ->  Index Only Scan using test_mode_a_idx on test_mode
+         Index Cond: (a = 2)
+(3 rows)
+
+select name, generic_plans, custom_plans from pg_prepared_statements
+  where  name = 'test_mode_pp';
+     name     | generic_plans | custom_plans 
+--------------+---------------+--------------
+ test_mode_pp |             0 |            1
+(1 row)
+
+-- force generic plan
+set plan_cache_mode to force_generic_plan;
+explain (costs off) execute test_mode_pp(2);
+         QUERY PLAN          
+-----------------------------
+ Aggregate
+   ->  Seq Scan on test_mode
+         Filter: (a = $1)
+(3 rows)
+
+select name, generic_plans, custom_plans from pg_prepared_statements
+  where  name = 'test_mode_pp';
+     name     | generic_plans | custom_plans 
+--------------+---------------+--------------
+ test_mode_pp |             1 |            1
+(1 row)
+
+-- get to generic plan by 5 executions
+set plan_cache_mode to auto;
+execute test_mode_pp(1); -- 1x
+ count 
+-------
+  1000
+(1 row)
+
+execute test_mode_pp(1); -- 2x
+ count 
+-------
+  1000
+(1 row)
+
+execute test_mode_pp(1); -- 3x
+ count 
+-------
+  1000
+(1 row)
+
+execute test_mode_pp(1); -- 4x
+ count 
+-------
+  1000
+(1 row)
+
+select name, generic_plans, custom_plans from pg_prepared_statements
+  where  name = 'test_mode_pp';
+     name     | generic_plans | custom_plans 
+--------------+---------------+--------------
+ test_mode_pp |             1 |            5
+(1 row)
+
+execute test_mode_pp(1); -- 5x
+ count 
+-------
+  1000
+(1 row)
+
+select name, generic_plans, custom_plans from pg_prepared_statements
+  where  name = 'test_mode_pp';
+     name     | generic_plans | custom_plans 
+--------------+---------------+--------------
+ test_mode_pp |             2 |            5
+(1 row)
+
+-- we should now get a really bad plan
+explain (costs off) execute test_mode_pp(2);
+         QUERY PLAN          
+-----------------------------
+ Aggregate
+   ->  Seq Scan on test_mode
+         Filter: (a = $1)
+(3 rows)
+
+-- but we can force a custom plan
+set plan_cache_mode to force_custom_plan;
+explain (costs off) execute test_mode_pp(2);
+                        QUERY PLAN                        
+----------------------------------------------------------
+ Aggregate
+   ->  Index Only Scan using test_mode_a_idx on test_mode
+         Index Cond: (a = 2)
+(3 rows)
+
+select name, generic_plans, custom_plans from pg_prepared_statements
+  where  name = 'test_mode_pp';
+     name     | generic_plans | custom_plans 
+--------------+---------------+--------------
+ test_mode_pp |             3 |            6
+(1 row)
+
+drop table test_mode;