Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit33e06eb

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 NeilConway. The view lists cursors defined by DECLARE CURSOR, using SPI, orvia the Bind message of the frontend/backend protocol. This means theview does not list the unnamed portal or the portal created to implementEXECUTE. Because we do list SPI portals, there might be more rows inthis 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 theview for cursors defined by DECLARE CURSOR is based ondebug_query_string. That means it is not accurate if multiple queriesseparated by semicolons are submitted as one query string. However,there doesn't seem a trivial fix for that: debug_query_stringis better than nothing. I also changed SPI_cursor_open() to includethe source text for the portal it creates: AFAICS there is no reasonnot to do this.Update the documentation and regression tests, bump the catversion.
1 parent558bc25 commit33e06eb

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
CREATEVIEWpg_rolesAS
@@ -148,6 +148,13 @@ CREATE VIEW pg_locks AS
148148
transactionid xid, classidoid, objidoid, objsubid int2,
149149
transaction xid, pid int4, modetext, grantedboolean);
150150

151+
CREATEVIEWpg_cursorsAS
152+
SELECTC.name,C.statement,C.is_holdable,C.is_binary,
153+
C.is_scrollable,C.creation_time
154+
FROM pg_cursor()AS C
155+
(nametext, statementtext, is_holdableboolean, is_binaryboolean,
156+
is_scrollableboolean, creation_timetimestamptz);
157+
151158
CREATEVIEWpg_prepared_xactsAS
152159
SELECTP.transaction,P.gid,P.prepared,
153160
U.rolnameAS owner,D.datnameAS 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)
[8]ページ先頭
©2009-2025 Movatter.jp