Skip to content

Commit 33e06eb

Browse files
author
Neil Conway
committed
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.
1 parent 558bc25 commit 33e06eb

File tree

17 files changed

+388
-32
lines changed

17 files changed

+388
-32
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 131 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
<!--
22
Documentation of the system catalogs, directed toward PostgreSQL developers
3-
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.117 2006/01/16 18:15:30 neilc Exp $
3+
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.118 2006/01/18 06:49:25 neilc Exp $
44
-->
55

66
<chapter id="catalogs">
@@ -4359,6 +4359,11 @@
43594359
</thead>
43604360

43614361
<tbody>
4362+
<row>
4363+
<entry><link linkend="view-pg-cursors"><structname>pg_cursors</structname></link></entry>
4364+
<entry>open cursors</entry>
4365+
</row>
4366+
43624367
<row>
43634368
<entry><link linkend="view-pg-group"><structname>pg_group</structname></link></entry>
43644369
<entry>groups of database users</entry>
@@ -4429,6 +4434,131 @@
44294434
</table>
44304435
</sect1>
44314436

4437+
<sect1 id="view-pg-cursors">
4438+
<title><structname>pg_cursors</structname></title>
4439+
4440+
<indexterm zone="view-pg-cursors">
4441+
<primary>pg_cursors</primary>
4442+
</indexterm>
4443+
4444+
<para>
4445+
The <structname>pg_cursors</structname> view lists the cursors that
4446+
are currently available. Cursors can be defined in several ways:
4447+
<itemizedlist>
4448+
<listitem>
4449+
<para>
4450+
via the <xref linkend="sql-declare" endterm="sql-declare-title">
4451+
statement in SQL
4452+
</para>
4453+
</listitem>
4454+
4455+
<listitem>
4456+
<para>
4457+
via the Bind message in the frontend/backend protocol, as
4458+
described in <xref linkend="protocol-flow-ext-query">
4459+
</para>
4460+
</listitem>
4461+
4462+
<listitem>
4463+
<para>
4464+
via the Server Programming Interface (SPI), as described in
4465+
<xref linkend="spi-interface">
4466+
</itemizedlist>
4467+
4468+
The <structname>pg_cursors</structname> view displays cursors
4469+
created by any of these means. Cursors only exist for the duration
4470+
of the transaction that defines them, unless they have been
4471+
declared <literal>WITH HOLD</literal>. Therefore non-holdable
4472+
cursors are only present in the view until the end of their
4473+
creating transaction.
4474+
4475+
<note>
4476+
<para>
4477+
Cursors are used internally to implement some of the components
4478+
of <productname>PostgreSQL</>, such as procedural languages.
4479+
Therefore, the <structname>pg_cursors</> view may include cursors
4480+
that have not been explicitly created by the user.
4481+
</para>
4482+
</note>
4483+
</para>
4484+
4485+
<table>
4486+
<title><structname>pg_cursors</> Columns</title>
4487+
4488+
<tgroup cols=4>
4489+
<thead>
4490+
<row>
4491+
<entry>Name</entry>
4492+
<entry>Type</entry>
4493+
<entry>References</entry>
4494+
<entry>Description</entry>
4495+
</row>
4496+
</thead>
4497+
4498+
<tbody>
4499+
<row>
4500+
<entry><structfield>name</structfield></entry>
4501+
<entry><type>text</type></entry>
4502+
<entry></entry>
4503+
<entry>The name of the cursor</entry>
4504+
</row>
4505+
4506+
<row>
4507+
<entry><structfield>statement</structfield></entry>
4508+
<entry><type>text</type></entry>
4509+
<entry></entry>
4510+
<entry>The verbatim query string submitted to declare this cursor</entry>
4511+
</row>
4512+
4513+
<row>
4514+
<entry><structfield>is_holdable</structfield></entry>
4515+
<entry><type>boolean</type></entry>
4516+
<entry></entry>
4517+
<entry>
4518+
<literal>true</literal> if the cursor is holdable (that is, it
4519+
can be accessed after the transaction that declared the cursor
4520+
has committed); <literal>false</literal> otherwise
4521+
</entry>
4522+
</row>
4523+
4524+
<row>
4525+
<entry><structfield>is_binary</structfield></entry>
4526+
<entry><type>boolean</type></entry>
4527+
<entry></entry>
4528+
<entry>
4529+
<literal>true</literal> if the cursor was declared
4530+
<literal>BINARY</literal>; <literal>false</literal>
4531+
otherwise
4532+
</entry>
4533+
</row>
4534+
4535+
<row>
4536+
<entry><structfield>is_scrollable</structfield></entry>
4537+
<entry><type>boolean</type></entry>
4538+
<entry></entry>
4539+
<entry>
4540+
<literal>true</> if the cursor is scrollable (that is, it
4541+
allows rows to be retrieved in a nonsequential manner);
4542+
<literal>false</literal> otherwise
4543+
</entry>
4544+
</row>
4545+
4546+
<row>
4547+
<entry><structfield>creation_time</structfield></entry>
4548+
<entry><type>timestamptz</type></entry>
4549+
<entry></entry>
4550+
<entry>The time at which the cursor was declared</entry>
4551+
</row>
4552+
</tbody>
4553+
</tgroup>
4554+
</table>
4555+
4556+
<para>
4557+
The <structname>pg_cursors</structname> view is read only.
4558+
</para>
4559+
4560+
</sect1>
4561+
44324562
<sect1 id="view-pg-group">
44334563
<title><structname>pg_group</structname></title>
44344564

‎doc/src/sgml/protocol.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.62 2005/08/14 22:19:49 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.63 2006/01/18 06:49:25 neilc Exp $ -->
22

33
<chapter id="protocol">
44
<title>Frontend/Backend Protocol</title>
@@ -602,7 +602,7 @@
602602
</para>
603603
</sect2>
604604

605-
<sect2>
605+
<sect2 id="protocol-flow-ext-query">
606606
<title>Extended Query</title>
607607

608608
<para>

‎doc/src/sgml/ref/close.sgml

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/close.sgml,v 1.22 2005/01/04 00:39:53 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/close.sgml,v 1.23 2006/01/18 06:49:26 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -76,6 +76,11 @@ CLOSE <replaceable class="PARAMETER">name</replaceable>
7676
<xref linkend="sql-declare" endterm="sql-declare-title">
7777
statement to declare a cursor.
7878
</para>
79+
80+
<para>
81+
You can see all available cursors by querying the
82+
<structname>pg_cursors</structname> system view.
83+
</para>
7984
</refsect1>
8085

8186
<refsect1>

‎doc/src/sgml/ref/declare.sgml

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.33 2005/01/04 00:39:53 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.34 2006/01/18 06:49:26 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -253,6 +253,11 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITI
253253
the standard SQL cursor conventions, including those involving
254254
<command>DECLARE</command> and <command>OPEN</command> statements.
255255
</para>
256+
257+
<para>
258+
You can see all available cursors by querying the
259+
<structname>pg_cursors</structname> system view.
260+
</para>
256261
</refsect1>
257262

258263
<refsect1>

‎src/backend/catalog/system_views.sql

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.24 2006/01/16 18:15:30 neilc Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.25 2006/01/18 06:49:26 neilc Exp $
77
*/
88

99
CREATE VIEW pg_roles AS
@@ -148,6 +148,13 @@ CREATE VIEW pg_locks AS
148148
transactionid xid, classid oid, objid oid, objsubid int2,
149149
transaction xid, pid int4, mode text, granted boolean);
150150

151+
CREATE VIEW pg_cursors AS
152+
SELECT C.name, C.statement, C.is_holdable, C.is_binary,
153+
C.is_scrollable, C.creation_time
154+
FROM pg_cursor() AS C
155+
(name text, statement text, is_holdable boolean, is_binary boolean,
156+
is_scrollable boolean, creation_time timestamptz);
157+
151158
CREATE VIEW pg_prepared_xacts AS
152159
SELECT P.transaction, P.gid, P.prepared,
153160
U.rolname AS owner, D.datname AS database

‎src/backend/commands/portalcmds.c

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.44 2005/11/03 17:11:35 alvherre Exp $
17+
* $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.45 2006/01/18 06:49:26 neilc Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -28,6 +28,7 @@
2828
#include "optimizer/planner.h"
2929
#include "rewrite/rewriteHandler.h"
3030
#include "tcop/pquery.h"
31+
#include "tcop/tcopprot.h"
3132
#include "utils/memutils.h"
3233

3334

@@ -105,8 +106,12 @@ PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params)
105106
query = copyObject(query);
106107
plan = copyObject(plan);
107108

109+
/*
110+
* XXX: debug_query_string is wrong here: the user might have
111+
* submitted more than one semicolon delimited queries.
112+
*/
108113
PortalDefineQuery(portal,
109-
NULL, /* unfortunately don't have sourceText */
114+
pstrdup(debug_query_string),
110115
"SELECT", /* cursor's query is always a SELECT */
111116
list_make1(query),
112117
list_make1(plan),

‎src/backend/commands/prepare.c

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Copyright (c) 2002-2005, PostgreSQL Global Development Group
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.46 2006/01/16 18:15:30 neilc Exp $
13+
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.47 2006/01/18 06:49:26 neilc Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -162,11 +162,11 @@ ExecuteQuery(ExecuteStmt *stmt, ParamListInfo params,
162162
paramLI = EvaluateParams(estate, stmt->params, entry->argtype_list);
163163
}
164164

165-
/*
166-
* Create a new portal to run the query in
167-
*/
165+
/* Create a new portal to run the query in */
168166
portal = CreateNewPortal();
169-
167+
/* Don't display the portal in pg_cursors, it is for internal use only */
168+
portal->visible = false;
169+
170170
/*
171171
* For CREATE TABLE / AS EXECUTE, make a copy of the stored query so that
172172
* we can modify its destination (yech, but this has always been ugly).

‎src/backend/executor/spi.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.145 2005/11/22 18:17:10 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.146 2006/01/18 06:49:27 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -921,8 +921,8 @@ SPI_cursor_open(const char *name, void *plan,
921921
* Set up the portal.
922922
*/
923923
PortalDefineQuery(portal,
924-
NULL, /* unfortunately don't have sourceText */
925-
"SELECT", /* nor the raw parse tree... */
924+
spiplan->query,
925+
"SELECT", /* don't have the raw parse tree... */
926926
list_make1(queryTree),
927927
list_make1(planTree),
928928
PortalGetHeapMemory(portal));

‎src/backend/tcop/postgres.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.478 2006/01/08 07:00:25 neilc Exp $
11+
* $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.479 2006/01/18 06:49:27 neilc Exp $
1212
*
1313
* NOTES
1414
* this is the "main" module of the postgres backend and
@@ -956,6 +956,8 @@ exec_simple_query(const char *query_string)
956956
* already is one, silently drop it.
957957
*/
958958
portal = CreatePortal("", true, true);
959+
/* Don't display the portal in pg_cursors */
960+
portal->visible = false;
959961

960962
PortalDefineQuery(portal,
961963
query_string,

0 commit comments

Comments
 (0)