Redesign query-snapshot timing so that volatile functions in READ COMMITTED

mode see a fresh snapshot for each command in the function, rather than
using the latest interactive command's snapshot.  Also, suppress fresh
snapshots as well as CommandCounterIncrement inside STABLE and IMMUTABLE
functions, instead using the snapshot taken for the most closely nested
regular query.  (This behavior is only sane for read-only functions, so
the patch also enforces that such functions contain only SELECT commands.)
As per my proposal of 6-Sep-2004; I note that I floated essentially the
same proposal on 19-Jun-2002, but that discussion tailed off without any
action.  Since 8.0 seems like the right place to be taking possibly
nontrivial backwards compatibility hits, let's get it done now.

3 files changed, 19 insertions, 10 deletions

diff --git a/src/include/executor/execdesc.h b/src/include/executor/execdesc.h
index 131fa727098..c22266a4c41 100644
--- a/src/include/executor/execdesc.h
+++ b/src/include/executor/execdesc.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/executor/execdesc.h,v 1.28 2004/08/29 04:13:06 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/executor/execdesc.h,v 1.29 2004/09/13 20:07:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -33,6 +33,8 @@ typedef struct QueryDesc
 	CmdType		operation;		/* CMD_SELECT, CMD_UPDATE, etc. */
 	Query	   *parsetree;		/* rewritten parsetree */
 	Plan	   *plantree;		/* planner's output */
+	Snapshot	snapshot;		/* snapshot to use for query */
+	Snapshot	crosscheck_snapshot;	/* crosscheck for RI update/delete */
 	DestReceiver *dest;			/* the destination for tuple output */
 	ParamListInfo params;		/* param values being passed in */
 	bool		doInstrument;	/* TRUE requests runtime instrumentation */
@@ -45,6 +47,8 @@ typedef struct QueryDesc
 
 /* in pquery.c */
 extern QueryDesc *CreateQueryDesc(Query *parsetree, Plan *plantree,
+				Snapshot snapshot,
+				Snapshot crosscheck_snapshot,
 				DestReceiver *dest,
 				ParamListInfo params,
 				bool doInstrument);
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 81b53d272c5..7f894f26d8b 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/executor/executor.h,v 1.112 2004/08/29 05:06:56 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/executor/executor.h,v 1.113 2004/09/13 20:07:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -95,8 +95,7 @@ extern HeapTuple ExecRemoveJunk(JunkFilter *junkfilter, TupleTableSlot *slot);
 /*
  * prototypes from functions in execMain.c
  */
-extern void ExecutorStart(QueryDesc *queryDesc, bool useCurrentSnapshot,
-			  bool explainOnly);
+extern void ExecutorStart(QueryDesc *queryDesc, bool explainOnly);
 extern TupleTableSlot *ExecutorRun(QueryDesc *queryDesc,
 			ScanDirection direction, long count);
 extern void ExecutorEnd(QueryDesc *queryDesc);
diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h
index ef10789c614..d58f66ad314 100644
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -2,7 +2,7 @@
  *
  * spi.h
  *
- * $PostgreSQL: pgsql/src/include/executor/spi.h,v 1.47 2004/08/29 05:06:56 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/executor/spi.h,v 1.48 2004/09/13 20:07:53 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -81,11 +81,17 @@ extern int	SPI_connect(void);
 extern int	SPI_finish(void);
 extern void SPI_push(void);
 extern void SPI_pop(void);
+extern int	SPI_execute(const char *src, bool read_only, int tcount);
+extern int	SPI_execute_plan(void *plan, Datum *Values, const char *Nulls,
+							 bool read_only, int tcount);
 extern int	SPI_exec(const char *src, int tcount);
-extern int SPI_execp(void *plan, Datum *values, const char *Nulls,
-		  int tcount);
-extern int SPI_execp_current(void *plan, Datum *values, const char *Nulls,
-				  bool useCurrentSnapshot, int tcount);
+extern int	SPI_execp(void *plan, Datum *Values, const char *Nulls,
+					  int tcount);
+extern int	SPI_execute_snapshot(void *plan,
+								 Datum *Values, const char *Nulls,
+								 Snapshot snapshot,
+								 Snapshot crosscheck_snapshot,
+								 bool read_only, int tcount);
 extern void *SPI_prepare(const char *src, int nargs, Oid *argtypes);
 extern void *SPI_saveplan(void *plan);
 extern int	SPI_freeplan(void *plan);
@@ -113,7 +119,7 @@ extern void SPI_freetuple(HeapTuple pointer);
 extern void SPI_freetuptable(SPITupleTable *tuptable);
 
 extern Portal SPI_cursor_open(const char *name, void *plan,
-				Datum *Values, const char *Nulls);
+				Datum *Values, const char *Nulls, bool read_only);
 extern Portal SPI_cursor_find(const char *name);
 extern void SPI_cursor_fetch(Portal portal, bool forward, int count);
 extern void SPI_cursor_move(Portal portal, bool forward, int count);