Make use of plancache module for SPI plans. In particular, since plpgsql

uses SPI plans, this finally fixes the ancient gotcha that you can't
drop and recreate a temp table used by a plpgsql function.

Along the way, clean up SPI's API a little bit by declaring SPI plan
pointers as "SPIPlanPtr" instead of "void *".  This is cosmetic but
helps to forestall simple programming mistakes.  (I have changed some
but not all of the callers to match; there are still some "void *"'s
in contrib and the PL's.  This is intentional so that we can see if
anyone's compiler complains about it.)

3 files changed, 104 insertions, 3 deletions

diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out
index 4980a9ab68b..cac9cfd2574 100644
--- a/src/test/regress/expected/plancache.out
+++ b/src/test/regress/expected/plancache.out
@@ -100,3 +100,64 @@ EXECUTE vprep;
  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)
+
diff --git a/src/test/regress/regress.c b/src/test/regress/regress.c
index 551b157a169..8f96382dfd6 100644
--- a/src/test/regress/regress.c
+++ b/src/test/regress/regress.c
@@ -1,5 +1,5 @@
 /*
- * $PostgreSQL: pgsql/src/test/regress/regress.c,v 1.69 2007/02/01 19:10:30 momjian Exp $
+ * $PostgreSQL: pgsql/src/test/regress/regress.c,v 1.70 2007/03/15 23:12:07 tgl Exp $
  */
 
 #include "postgres.h"
@@ -451,7 +451,7 @@ extern Datum set_ttdummy(PG_FUNCTION_ARGS);
 
 #define TTDUMMY_INFINITY	999999
 
-static void *splan = NULL;
+static SPIPlanPtr splan = NULL;
 static bool ttoff = false;
 
 PG_FUNCTION_INFO_V1(ttdummy);
@@ -599,7 +599,7 @@ ttdummy(PG_FUNCTION_ARGS)
 	/* if there is no plan ... */
 	if (splan == NULL)
 	{
-		void	   *pplan;
+		SPIPlanPtr	pplan;
 		Oid		   *ctypes;
 		char	   *query;
 
diff --git a/src/test/regress/sql/plancache.sql b/src/test/regress/sql/plancache.sql
index b952efe1972..0b34a62c3bd 100644
--- a/src/test/regress/sql/plancache.sql
+++ b/src/test/regress/sql/plancache.sql
@@ -51,3 +51,43 @@ EXECUTE vprep;
 CREATE OR REPLACE TEMP VIEW voo AS SELECT q1, q2/2 AS q2 FROM foo;
 
 EXECUTE vprep;
+
+-- 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);
+select cache_test(2);
+select cache_test(3);
+
+-- 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();
+
+create or replace temp view v1 as
+  select 2+2+4 as f1;
+select cache_test_2();
+
+create or replace temp view v1 as
+  select 2+2+4+(select max(unique1) from tenk1) as f1;
+select cache_test_2();