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.

3 files changed, 88 insertions, 1 deletions

diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index 3f0e0cdd265..cbff0b4245a 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -676,7 +676,30 @@ CLOSE foo10;
 CLOSE foo11;
 CLOSE foo12;
 -- leave some cursors open, to test that auto-close works.
+-- record this in the system view as well (don't query the time field there
+-- however)
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name  |                      statement                       | is_holdable | is_binary | is_scrollable 
+-------+------------------------------------------------------+-------------+-----------+---------------
+ foo13 | DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1; | f           | f         | t
+ foo15 | DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1; | f           | f         | t
+ foo19 | DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1; | f           | f         | t
+ foo17 | DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1; | f           | f         | t
+ foo14 | DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2; | f           | f         | t
+ foo21 | DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1; | f           | f         | t
+ foo23 | DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1; | f           | f         | t
+ foo18 | DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2; | f           | f         | t
+ foo20 | DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2; | f           | f         | t
+ foo22 | DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2; | f           | f         | t
+ foo16 | DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2; | f           | f         | t
+(11 rows)
+
 END;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable 
+------+-----------+-------------+-----------+---------------
+(0 rows)
+
 --
 -- NO SCROLL disallows backward fetching
 --
@@ -695,6 +718,11 @@ END;
 --
 -- Cursors outside transaction blocks
 --
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable 
+------+-----------+-------------+-----------+---------------
+(0 rows)
+
 BEGIN;
 DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
 FETCH FROM foo25;
@@ -728,6 +756,12 @@ FETCH ABSOLUTE -1 FROM foo25;
     2968 |    9999 |   0 |    0 |   8 |      8 |      68 |      968 |         968 |      2968 |     2968 | 136 |  137 | EKAAAA   | PUOAAA   | VVVVxx
 (1 row)
 
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name  |                           statement                            | is_holdable | is_binary | is_scrollable 
+-------+----------------------------------------------------------------+-------------+-----------+---------------
+ foo25 | DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; | t           | f         | t
+(1 row)
+
 CLOSE foo25;
 --
 -- ROLLBACK should close holdable cursors
@@ -808,3 +842,30 @@ fetch all from c2;
 
 drop function count_tt1_v();
 drop function count_tt1_s();
+-- Create a cursor with the BINARY option and check the pg_cursors view
+BEGIN;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name |                              statement                               | is_holdable | is_binary | is_scrollable 
+------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2   | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t           | f         | f
+(1 row)
+
+DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name |                              statement                               | is_holdable | is_binary | is_scrollable 
+------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2   | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t           | f         | f
+ bc   | DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1;                    | f           | t         | t
+(2 rows)
+
+ROLLBACK;
+-- We should not see the portal that is created internally to
+-- implement EXECUTE in pg_cursors
+PREPARE cprep AS
+  SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+EXECUTE cprep;
+ name |                              statement                               | is_holdable | is_binary | is_scrollable 
+------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2   | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t           | f         | f
+(1 row)
+
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 63c06ec75ac..90e38d013f3 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1277,6 +1277,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
          viewname         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                definition                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
 --------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  iexit                    | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
+ pg_cursors               | SELECT c.name, c."statement", c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name text, "statement" text, is_holdable boolean, is_binary boolean, is_scrollable boolean, creation_time timestamp with time zone);
  pg_group                 | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
  pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
  pg_locks                 | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid, l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid, relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction" xid, pid integer, "mode" text, "granted" boolean);
@@ -1321,7 +1322,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
  shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
  street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
  toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
-(45 rows)
+(46 rows)
 
 SELECT tablename, rulename, definition FROM pg_rules 
 	ORDER BY tablename, rulename;
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index da4e3b0e3ae..c0307874e80 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -168,8 +168,14 @@ CLOSE foo12;
 
 -- leave some cursors open, to test that auto-close works.
 
+-- record this in the system view as well (don't query the time field there
+-- however)
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
 END;
 
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
 --
 -- NO SCROLL disallows backward fetching
 --
@@ -188,6 +194,9 @@ END;
 -- Cursors outside transaction blocks
 --
 
+
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
 BEGIN;
 
 DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
@@ -204,6 +213,8 @@ FETCH BACKWARD FROM foo25;
 
 FETCH ABSOLUTE -1 FROM foo25;
 
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
 CLOSE foo25;
 
 --
@@ -278,3 +289,17 @@ fetch all from c2;
 
 drop function count_tt1_v();
 drop function count_tt1_s();
+
+
+-- Create a cursor with the BINARY option and check the pg_cursors view
+BEGIN;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1;
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ROLLBACK;
+
+-- We should not see the portal that is created internally to
+-- implement EXECUTE in pg_cursors
+PREPARE cprep AS
+  SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+EXECUTE cprep;