Ensure that a cursor is scanned under the same scanCommandId it was

originally created with, so that the set of visible tuples does not
change as a result of other activity.  This essentially makes PG cursors
INSENSITIVE per the SQL92 definition.  See bug report of 13-Feb-02.

3 files changed, 43 insertions, 10 deletions

diff --git a/src/backend/commands/command.c b/src/backend/commands/command.c
index 646511eb18d..9b1a33673c7 100644
--- a/src/backend/commands/command.c
+++ b/src/backend/commands/command.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.152 2002/01/03 23:19:30 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.153 2002/02/14 15:24:06 tgl Exp $
  *
  * NOTES
  *	  The PerformAddAttribute() code, like most of the relation
@@ -103,6 +103,7 @@ PerformPortalFetch(char *name,
 	QueryDesc  *queryDesc;
 	EState	   *estate;
 	MemoryContext oldcontext;
+	CommandId	savedId;
 	bool		temp_desc = false;
 
 	/*
@@ -156,7 +157,7 @@ PerformPortalFetch(char *name,
 	}
 
 	/*
-	 * tell the destination to prepare to receive some tuples.
+	 * Tell the destination to prepare to receive some tuples.
 	 */
 	BeginCommand(name,
 				 queryDesc->operation,
@@ -169,6 +170,14 @@ PerformPortalFetch(char *name,
 				 queryDesc->dest);
 
 	/*
+	 * Restore the scanCommandId that was current when the cursor was
+	 * opened.  This ensures that we see the same tuples throughout the
+	 * execution of the cursor.
+	 */
+	savedId = GetScanCommandId();
+	SetScanCommandId(PortalGetCommandId(portal));
+
+	/*
 	 * Determine which direction to go in, and check to see if we're
 	 * already at the end of the available tuples in that direction.  If
 	 * so, do nothing.	(This check exists because not all plan node types
@@ -215,6 +224,11 @@ PerformPortalFetch(char *name,
 	}
 
 	/*
+	 * Restore outer command ID.
+	 */
+	SetScanCommandId(savedId);
+
+	/*
 	 * Clean up and switch back to old context.
 	 */
 	if (temp_desc)
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 064b0255222..b9ab6422658 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/executor/spi.c,v 1.64 2002/01/03 20:30:47 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/executor/spi.c,v 1.65 2002/02/14 15:24:08 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -740,9 +740,9 @@ SPI_cursor_open(char *name, void *plan, Datum *Values, char *Nulls)
 	_SPI_current->processed = 0;
 	_SPI_current->tuptable = NULL;
 
-	/* Make up a portal name if none given */
 	if (name == NULL)
 	{
+		/* Make up a portal name if none given */
 		for (;;)
 		{
 			unnamed_portal_count++;
@@ -755,11 +755,13 @@ SPI_cursor_open(char *name, void *plan, Datum *Values, char *Nulls)
 
 		name = portalname;
 	}
-
-	/* Ensure the portal doesn't exist already */
-	portal = GetPortalByName(name);
-	if (portal != NULL)
-		elog(ERROR, "cursor \"%s\" already in use", name);
+	else
+	{
+		/* Ensure the portal doesn't exist already */
+		portal = GetPortalByName(name);
+		if (portal != NULL)
+			elog(ERROR, "cursor \"%s\" already in use", name);
+	}
 
 	/* Create the portal */
 	portal = CreatePortal(name);
@@ -1228,6 +1230,7 @@ _SPI_cursor_operation(Portal portal, bool forward, int count,
 	QueryDesc  *querydesc;
 	EState	   *estate;
 	MemoryContext oldcontext;
+	CommandId	savedId;
 	CommandDest olddest;
 
 	/* Check that the portal is valid */
@@ -1245,6 +1248,7 @@ _SPI_cursor_operation(Portal portal, bool forward, int count,
 
 	/* Switch to the portals memory context */
 	oldcontext = MemoryContextSwitchTo(PortalGetHeapMemory(portal));
+
 	querydesc = PortalGetQueryDesc(portal);
 	estate = PortalGetState(portal);
 
@@ -1253,6 +1257,14 @@ _SPI_cursor_operation(Portal portal, bool forward, int count,
 	olddest = querydesc->dest;
 	querydesc->dest = dest;
 
+	/*
+	 * Restore the scanCommandId that was current when the cursor was
+	 * opened.  This ensures that we see the same tuples throughout the
+	 * execution of the cursor.
+	 */
+	savedId = GetScanCommandId();
+	SetScanCommandId(PortalGetCommandId(portal));
+
 	/* Run the executor like PerformPortalFetch and remember states */
 	if (forward)
 	{
@@ -1279,6 +1291,11 @@ _SPI_cursor_operation(Portal portal, bool forward, int count,
 		}
 	}
 
+	/*
+	 * Restore outer command ID.
+	 */
+	SetScanCommandId(savedId);
+
 	/* Restore the old command destination and switch back to callers */
 	/* memory context */
 	querydesc->dest = olddest;
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index 25171ea7bee..76827529224 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/utils/mmgr/portalmem.c,v 1.44 2001/10/25 05:49:51 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/utils/mmgr/portalmem.c,v 1.45 2002/02/14 15:24:09 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -168,6 +168,7 @@ PortalSetQuery(Portal portal,
 
 	portal->queryDesc = queryDesc;
 	portal->attinfo = attinfo;
+	portal->commandId = GetScanCommandId();
 	portal->state = state;
 	portal->atStart = true;		/* Allow fetch forward only */
 	portal->atEnd = false;
@@ -213,6 +214,7 @@ CreatePortal(char *name)
 	/* initialize portal query */
 	portal->queryDesc = NULL;
 	portal->attinfo = NULL;
+	portal->commandId = 0;
 	portal->state = NULL;
 	portal->atStart = true;		/* disallow fetches until query is set */
 	portal->atEnd = true;