Add a new system view, pg_cursors, that displays the currently available

cursors. Patch from Joachim Wieland, review and ediorialization by Neil
Conway. The view lists cursors defined by DECLARE CURSOR, using SPI, or
via the Bind message of the frontend/backend protocol. This means the
view does not list the unnamed portal or the portal created to implement
EXECUTE. Because we do list SPI portals, there might be more rows in
this view than you might expect if you are using SPI implicitly (e.g.
via a procedural language).

Per recent discussion on -hackers, the query string included in the
view for cursors defined by DECLARE CURSOR is based on
debug_query_string. That means it is not accurate if multiple queries
separated by semicolons are submitted as one query string. However,
there doesn't seem a trivial fix for that: debug_query_string
is better than nothing. I also changed SPI_cursor_open() to include
the source text for the portal it creates: AFAICS there is no reason
not to do this.

Update the documentation and regression tests, bump the catversion.

6 files changed, 141 insertions, 20 deletions

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c44e9ed72f1..a6d8155a0a4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 1996-2005, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.24 2006/01/16 18:15:30 neilc Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.25 2006/01/18 06:49:26 neilc Exp $
  */
 
 CREATE VIEW pg_roles AS 
@@ -148,6 +148,13 @@ CREATE VIEW pg_locks AS
      transactionid xid, classid oid, objid oid, objsubid int2,
      transaction xid, pid int4, mode text, granted boolean);
 
+CREATE VIEW pg_cursors AS
+    SELECT C.name, C.statement, C.is_holdable, C.is_binary,
+           C.is_scrollable, C.creation_time
+    FROM pg_cursor() AS C
+         (name text, statement text, is_holdable boolean, is_binary boolean,
+          is_scrollable boolean, creation_time timestamptz);
+
 CREATE VIEW pg_prepared_xacts AS
     SELECT P.transaction, P.gid, P.prepared,
            U.rolname AS owner, D.datname AS database
diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c
index 8246b25774e..b2dab9d98de 100644
--- a/src/backend/commands/portalcmds.c
+++ b/src/backend/commands/portalcmds.c
@@ -14,7 +14,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.44 2005/11/03 17:11:35 alvherre Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.45 2006/01/18 06:49:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -28,6 +28,7 @@
 #include "optimizer/planner.h"
 #include "rewrite/rewriteHandler.h"
 #include "tcop/pquery.h"
+#include "tcop/tcopprot.h"
 #include "utils/memutils.h"
 
 
@@ -105,8 +106,12 @@ PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params)
 	query = copyObject(query);
 	plan = copyObject(plan);
 
+	/*
+	 * XXX: debug_query_string is wrong here: the user might have
+	 * submitted more than one semicolon delimited queries.
+	 */
 	PortalDefineQuery(portal,
-					  NULL,		/* unfortunately don't have sourceText */
+					  pstrdup(debug_query_string),
 					  "SELECT", /* cursor's query is always a SELECT */
 					  list_make1(query),
 					  list_make1(plan),
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index f523984e5a8..f0afdbba367 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -10,7 +10,7 @@
  * Copyright (c) 2002-2005, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.46 2006/01/16 18:15:30 neilc Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.47 2006/01/18 06:49:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -162,11 +162,11 @@ ExecuteQuery(ExecuteStmt *stmt, ParamListInfo params,
 		paramLI = EvaluateParams(estate, stmt->params, entry->argtype_list);
 	}
 
-	/*
-	 * Create a new portal to run the query in
-	 */
+	/* Create a new portal to run the query in */
 	portal = CreateNewPortal();
-
+	/* Don't display the portal in pg_cursors, it is for internal use only */
+	portal->visible = false;
+	
 	/*
 	 * For CREATE TABLE / AS EXECUTE, make a copy of the stored query so that
 	 * we can modify its destination (yech, but this has always been ugly).
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 21a9a901d62..278860600b4 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.145 2005/11/22 18:17:10 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.146 2006/01/18 06:49:27 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -921,8 +921,8 @@ SPI_cursor_open(const char *name, void *plan,
 	 * Set up the portal.
 	 */
 	PortalDefineQuery(portal,
-					  NULL,		/* unfortunately don't have sourceText */
-					  "SELECT", /* nor the raw parse tree... */
+					  spiplan->query,
+					  "SELECT", /* don't have the raw parse tree... */
 					  list_make1(queryTree),
 					  list_make1(planTree),
 					  PortalGetHeapMemory(portal));
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 0fe8ee057d5..ca08849afe9 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.478 2006/01/08 07:00:25 neilc Exp $
+ *	  $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.479 2006/01/18 06:49:27 neilc Exp $
  *
  * NOTES
  *	  this is the "main" module of the postgres backend and
@@ -956,6 +956,8 @@ exec_simple_query(const char *query_string)
 		 * already is one, silently drop it.
 		 */
 		portal = CreatePortal("", true, true);
+		/* Don't display the portal in pg_cursors */
+		portal->visible = false;
 
 		PortalDefineQuery(portal,
 						  query_string,
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index 0402005a372..1bd9cc61d85 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -12,15 +12,19 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/mmgr/portalmem.c,v 1.83 2005/11/22 18:17:27 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/mmgr/portalmem.c,v 1.84 2006/01/18 06:49:27 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
 #include "postgres.h"
 
-#include "miscadmin.h"
+#include "access/heapam.h"
+#include "catalog/pg_type.h"
 #include "commands/portalcmds.h"
 #include "executor/executor.h"
+#include "funcapi.h"
+#include "miscadmin.h"
+#include "utils/builtins.h"
 #include "utils/hsearch.h"
 #include "utils/memutils.h"
 #include "utils/portal.h"
@@ -56,8 +60,8 @@ do { \
 	\
 	MemSet(key, 0, MAX_PORTALNAME_LEN); \
 	StrNCpy(key, NAME, MAX_PORTALNAME_LEN); \
-	hentry = (PortalHashEnt*)hash_search(PortalHashTable, \
-										 key, HASH_FIND, NULL); \
+	hentry = (PortalHashEnt *) hash_search(PortalHashTable, \
+										   key, HASH_FIND, NULL);	\
 	if (hentry) \
 		PORTAL = hentry->portal; \
 	else \
@@ -70,8 +74,8 @@ do { \
 	\
 	MemSet(key, 0, MAX_PORTALNAME_LEN); \
 	StrNCpy(key, NAME, MAX_PORTALNAME_LEN); \
-	hentry = (PortalHashEnt*)hash_search(PortalHashTable, \
-										 key, HASH_ENTER, &found); \
+	hentry = (PortalHashEnt *) hash_search(PortalHashTable, \
+										   key, HASH_ENTER, &found);	\
 	if (found) \
 		elog(ERROR, "duplicate portal name"); \
 	hentry->portal = PORTAL; \
@@ -85,8 +89,8 @@ do { \
 	\
 	MemSet(key, 0, MAX_PORTALNAME_LEN); \
 	StrNCpy(key, PORTAL->name, MAX_PORTALNAME_LEN); \
-	hentry = (PortalHashEnt*)hash_search(PortalHashTable, \
-										 key, HASH_REMOVE, NULL); \
+	hentry = (PortalHashEnt *) hash_search(PortalHashTable, \
+										   key, HASH_REMOVE, NULL); \
 	if (hentry == NULL) \
 		elog(WARNING, "trying to delete portal name that does not exist"); \
 } while(0)
@@ -190,12 +194,15 @@ CreatePortal(const char *name, bool allowDup, bool dupSilent)
 										   "Portal");
 
 	/* initialize portal fields that don't start off zero */
+	portal->status = PORTAL_NEW;
 	portal->cleanup = PortalCleanup;
 	portal->createSubid = GetCurrentSubTransactionId();
 	portal->strategy = PORTAL_MULTI_QUERY;
 	portal->cursorOptions = CURSOR_OPT_NO_SCROLL;
 	portal->atStart = true;
 	portal->atEnd = true;		/* disallow fetches until query is set */
+	portal->visible = true;
+	portal->creation_time = GetCurrentTimestamp();
 
 	/* put portal in table (sets portal->name) */
 	PortalHashTableInsert(portal, name);
@@ -756,3 +763,103 @@ AtSubCleanup_Portals(SubTransactionId mySubid)
 		PortalDrop(portal, false);
 	}
 }
+
+/* Find all available cursors */
+Datum
+pg_cursor(PG_FUNCTION_ARGS)
+{
+	FuncCallContext	   *funcctx;
+	HASH_SEQ_STATUS    *hash_seq;
+	PortalHashEnt	   *hentry;
+
+	/* stuff done only on the first call of the function */
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext		oldcontext;
+		TupleDesc			tupdesc;
+
+		/* create a function context for cross-call persistence */
+		funcctx = SRF_FIRSTCALL_INIT();
+
+		/*
+		 * switch to memory context appropriate for multiple function
+		 * calls
+		 */
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		if (PortalHashTable)
+		{
+			hash_seq = (HASH_SEQ_STATUS *) palloc(sizeof(HASH_SEQ_STATUS));
+			hash_seq_init(hash_seq, PortalHashTable);
+			funcctx->user_fctx = (void *) hash_seq;
+		}
+		else
+			funcctx->user_fctx = NULL;
+
+		/*
+		 * build tupdesc for result tuples. This must match the
+		 * definition of the pg_cursors view in system_views.sql
+		 */
+		tupdesc = CreateTemplateTupleDesc(6, false);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement",
+						   TEXTOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "is_holdable",
+						   BOOLOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 4, "is_binary",
+						   BOOLOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 5, "is_scrollable",
+						   BOOLOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 6, "creation_time",
+						   TIMESTAMPTZOID, -1, 0);
+
+		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	/* stuff done on every call of the function */
+	funcctx = SRF_PERCALL_SETUP();
+	hash_seq = (HASH_SEQ_STATUS *) funcctx->user_fctx;
+
+	/* if the hash table is uninitialized, we're done */
+	if (hash_seq == NULL)
+		SRF_RETURN_DONE(funcctx);
+
+	/* loop until we find a visible portal or hit the end of the list */
+	while ((hentry = hash_seq_search(hash_seq)) != NULL)
+	{
+		if (hentry->portal->visible)
+			break;
+	}
+
+	if (hentry)
+	{
+		Portal		portal;
+		Datum		result;
+		HeapTuple	tuple;
+		Datum		values[6];
+		bool		nulls[6];
+
+		portal = hentry->portal;
+		MemSet(nulls, 0, sizeof(nulls));
+
+		values[0] = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
+		if (!portal->sourceText)
+			nulls[1] = true;
+		else
+			values[1] = DirectFunctionCall1(textin,
+											CStringGetDatum(portal->sourceText));
+		values[2] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_HOLD);
+		values[3] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_BINARY);
+		values[4] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_SCROLL);
+		values[5] = TimestampTzGetDatum(portal->creation_time);
+
+		tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+		result = HeapTupleGetDatum(tuple);
+		SRF_RETURN_NEXT(funcctx, result);
+	}
+
+	SRF_RETURN_DONE(funcctx);
+}
+