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

Commitaa83bc0

Browse files
committed
Restructure parsetree representation of DECLARE CURSOR: now it's a
utility statement (DeclareCursorStmt) with a SELECT query dangling fromit, rather than a SELECT query with a few unusual fields in it. Addcode to determine whether a planned query can safely be run backwards.If DECLARE CURSOR specifies SCROLL, ensure that the plan can be runbackwards by adding a Materialize plan node if it can't. Without SCROLL,you get an error if you try to fetch backwards from a cursor that can'thandle it. (There is still some discussion about what the exactbehavior should be, but this is necessary infrastructure in any case.)Along the way, make EXPLAIN DECLARE CURSOR work.
1 parentb9e8ffc commitaa83bc0

40 files changed

+661
-571
lines changed

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

Lines changed: 26 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/declare.sgml,v 1.18 2002/05/18 15:44:47 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/declare.sgml,v 1.19 2003/03/10 03:53:48 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -47,8 +47,7 @@ DECLARE <replaceable class="parameter">cursorname</replaceable> [ BINARY ] [ INS
4747
<term>BINARY</term>
4848
<listitem>
4949
<para>
50-
Causes the cursor to fetch data in binary
51-
rather than in text format.
50+
Causes the cursor to return data in binary rather than in text format.
5251
</para>
5352
</listitem>
5453
</varlistentry>
@@ -70,9 +69,8 @@ DECLARE <replaceable class="parameter">cursorname</replaceable> [ BINARY ] [ INS
7069
<term>SCROLL</term>
7170
<listitem>
7271
<para>
73-
<acronym>SQL92</acronym> keyword indicating that data may be retrieved
74-
in multiple rows per FETCH operation. Since this is allowed at all times
75-
by <productname>PostgreSQL</productname> this keyword has no effect.
72+
Specifies that the cursor may be used to retrieve rows
73+
in a nonsequential fashion (e.g., backwards).
7674
</para>
7775
</listitem>
7876
</varlistentry>
@@ -81,10 +79,10 @@ DECLARE <replaceable class="parameter">cursorname</replaceable> [ BINARY ] [ INS
8179
<term><replaceable class="parameter">query</replaceable></term>
8280
<listitem>
8381
<para>
84-
An SQL query which will provide the rows to be governed by the
85-
cursor.
86-
Refer tothe SELECT statement for further information about
87-
valid arguments.
82+
A <command>SELECT</> query which will provide the rows to be
83+
returned by thecursor.
84+
Refer to<xref linkend="sql-select" endterm="sql-select-title">
85+
for further information aboutvalid arguments.
8886
</para>
8987
</listitem>
9088
</varlistentry>
@@ -126,6 +124,10 @@ DECLARE <replaceable class="parameter">cursorname</replaceable> [ BINARY ] [ INS
126124

127125
</variablelist>
128126
</para>
127+
128+
<para>
129+
The BINARY, INSENSITIVE, and SCROLL keywords may appear in any order.
130+
</para>
129131
</refsect2>
130132

131133
<refsect2 id="R2-SQL-DECLARE-2">
@@ -193,9 +195,8 @@ ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks
193195
</para>
194196

195197
<para>
196-
Normal cursors return data in text format, either ASCII or another
197-
encoding scheme depending on how the <productname>PostgreSQL</productname>
198-
backend was built. Since
198+
Normal cursors return data in text format, the same as a <command>SELECT</>
199+
would produce. Since
199200
data is stored natively in binary format, the system must
200201
do a conversion to produce the text format. In addition,
201202
text formats are often larger in size than the corresponding binary format.
@@ -228,15 +229,11 @@ ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks
228229
representations (e.g., <quote>big-endian</quote> versus <quote>little-endian</quote>),
229230
you will probably not want your data returned in
230231
binary format.
231-
However, binary cursors may be a
232-
little more efficient since there is less conversion overhead in
233-
the server to client data transfer.
234232

235233
<tip>
236234
<para>
237-
If you intend to display the data in
238-
ASCII, getting it back in ASCII will save you some
239-
effort on the client side.
235+
If you intend to display the data as text, retrieving it in text form
236+
will save you some effort on the client side.
240237
</para>
241238
</tip>
242239
</para>
@@ -250,14 +247,23 @@ ERROR: DECLARE CURSOR may only be used in begin/end transaction blocks
250247
</title>
251248

252249
<para>
253-
Cursors are only availablein transactions. Use to
250+
Cursors are only availablewithin transactions. Use
254251
<xref linkend="sql-begin" endterm="sql-begin-title">,
255252
<xref linkend="sql-commit" endterm="sql-commit-title">
256253
and
257254
<xref linkend="sql-rollback" endterm="sql-rollback-title">
258255
to define a transaction block.
259256
</para>
260257

258+
<para>
259+
The <literal>SCROLL</> option should be specified when defining a cursor
260+
that will be used to fetch backwards. This is required by
261+
<acronym>SQL92</acronym>. However, for compatibility with
262+
earlier versions, <productname>PostgreSQL</productname> will allow
263+
backward fetches without <literal>SCROLL</>, if the cursor's query plan
264+
is simple enough that no extra overhead is needed to support it.
265+
</para>
266+
261267
<para>
262268
In <acronym>SQL92</acronym> cursors are only available in
263269
embedded <acronym>SQL</acronym> (<acronym>ESQL</acronym>) applications.

‎doc/src/sgml/ref/explain.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.23 2003/02/02 23:46:37 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.24 2003/03/10 03:53:49 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -56,7 +56,8 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="PARAMETER">query</replaceabl
5656
<listitem>
5757
<para>
5858
Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
59-
<command>DELETE</>, or <command>EXECUTE</> query.
59+
<command>DELETE</>, <command>EXECUTE</>,
60+
or <command>DECLARE CURSOR</> query.
6061
</para>
6162
</listitem>
6263
</varlistentry>

‎doc/src/sgml/ref/fetch.sgml

Lines changed: 14 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.25 2003/02/04 11:23:58 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.26 2003/03/10 03:53:49 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -13,7 +13,7 @@ PostgreSQL documentation
1313
FETCH
1414
</refname>
1515
<refpurpose>
16-
retrieve rows from atable using a cursor
16+
retrieve rows from aquery using a cursor
1717
</refpurpose>
1818
</refnamediv>
1919
<refsynopsisdiv>
@@ -66,7 +66,7 @@ FETCH [ FORWARD | BACKWARD | RELATIVE ] [ <replaceable class="PARAMETER">#</repl
6666
<term>RELATIVE</term>
6767
<listitem>
6868
<para>
69-
Noise word for SQL92 compatibility.
69+
Same as FORWARD; provided for SQL92 compatibility.
7070
</para>
7171
</listitem>
7272
</varlistentry>
@@ -247,13 +247,20 @@ WARNING: FETCH/ABSOLUTE not supported, using RELATIVE
247247
</title>
248248

249249
<para>
250-
Note that the FORWARD, BACKWARD, and ALL keywords are
250+
A cursor to be used in backwards fetching should be declared with the
251+
SCROLL option. In simple cases, <productname>PostgreSQL</productname>
252+
will allow backwards fetch from cursors not declared with SCROLL, but
253+
this behavior is best not relied on.
254+
</para>
255+
256+
<para>
257+
The FORWARD, BACKWARD, and ALL keywords are
251258
<productname>PostgreSQL</productname> extensions.
252259
See below for details on compatibility issues.
253260
</para>
254261

255262
<para>
256-
Updating datain a cursor is not supported by
263+
Updating datavia a cursor is not supported by
257264
<productname>PostgreSQL</productname>,
258265
because mapping cursor updates back to base tables is
259266
not generally possible, as is also the case with VIEW updates.
@@ -262,8 +269,7 @@ WARNING: FETCH/ABSOLUTE not supported, using RELATIVE
262269
</para>
263270

264271
<para>
265-
Cursors may only be used inside of transactions because
266-
the data that they store spans multiple user queries.
272+
Cursors may only be used inside transaction blocks.
267273
</para>
268274

269275
<para>
@@ -288,7 +294,7 @@ WARNING: FETCH/ABSOLUTE not supported, using RELATIVE
288294
</title>
289295

290296
<para>
291-
The followingexamples traverses a table using a cursor.
297+
The followingexample traverses a table using a cursor.
292298

293299
<programlisting>
294300
-- Set up and use a cursor:

‎doc/src/sgml/ref/move.sgml

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/move.sgml,v 1.18 2003/02/04 11:23:58 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/move.sgml,v 1.19 2003/03/10 03:53:49 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -88,13 +88,11 @@ DECLARE liahona CURSOR FOR SELECT * FROM films;
8888
-- Skip first 5 rows:
8989
MOVE FORWARD 5 IN liahona;
9090
<computeroutput>
91-
MOVE
91+
MOVE 5
9292
</computeroutput>
9393
-- Fetch 6th row in the cursor liahona:
9494
FETCH 1 IN liahona;
9595
<computeroutput>
96-
FETCH
97-
9896
code | title | did | date_prod | kind | len
9997
-------+--------+-----+-----------+--------+-------
10098
P_303 | 48 Hrs | 103 | 1982-10-22| Action | 01:37

‎doc/src/sgml/ref/prepare.sgml

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.2 2003/02/02 23:46:37 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.3 2003/03/10 03:53:49 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -54,6 +54,15 @@ PostgreSQL documentation
5454
</para>
5555
</listitem>
5656
</varlistentry>
57+
<varlistentry>
58+
<term><replaceable class="PARAMETER">query</replaceable></term>
59+
<listitem>
60+
<para>
61+
Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
62+
or <command>DELETE</> query.
63+
</para>
64+
</listitem>
65+
</varlistentry>
5766
</variablelist>
5867
</para>
5968
</refsect2>

‎src/backend/commands/explain.c

Lines changed: 33 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994-5, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/commands/explain.c,v 1.103 2003/02/1017:06:23 tgl Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/commands/explain.c,v 1.104 2003/03/1003:53:49 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -85,7 +85,9 @@ ExplainQuery(ExplainStmt *stmt, CommandDest dest)
8585
if (query->commandType==CMD_UTILITY)
8686
{
8787
/* Rewriter will not cope with utility statements */
88-
if (query->utilityStmt&&IsA(query->utilityStmt,ExecuteStmt))
88+
if (query->utilityStmt&&IsA(query->utilityStmt,DeclareCursorStmt))
89+
ExplainOneQuery(query,stmt,tstate);
90+
elseif (query->utilityStmt&&IsA(query->utilityStmt,ExecuteStmt))
8991
ExplainExecuteQuery(stmt,tstate);
9092
else
9193
do_text_output_oneline(tstate,"Utility statements have no plan structure");
@@ -125,30 +127,45 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate)
125127
{
126128
Plan*plan;
127129
QueryDesc*queryDesc;
130+
boolisCursor= false;
131+
intcursorOptions=0;
128132

129133
/* planner will not cope with utility statements */
130134
if (query->commandType==CMD_UTILITY)
131135
{
132-
if (query->utilityStmt&&IsA(query->utilityStmt,NotifyStmt))
136+
if (query->utilityStmt&&IsA(query->utilityStmt,DeclareCursorStmt))
137+
{
138+
DeclareCursorStmt*dcstmt;
139+
List*rewritten;
140+
141+
dcstmt= (DeclareCursorStmt*)query->utilityStmt;
142+
query= (Query*)dcstmt->query;
143+
isCursor= true;
144+
cursorOptions=dcstmt->options;
145+
/* Still need to rewrite cursor command */
146+
Assert(query->commandType==CMD_SELECT);
147+
rewritten=QueryRewrite(query);
148+
if (length(rewritten)!=1)
149+
elog(ERROR,"ExplainOneQuery: unexpected rewrite result");
150+
query= (Query*)lfirst(rewritten);
151+
Assert(query->commandType==CMD_SELECT);
152+
/* do not actually execute the underlying query! */
153+
stmt->analyze= false;
154+
}
155+
elseif (query->utilityStmt&&IsA(query->utilityStmt,NotifyStmt))
156+
{
133157
do_text_output_oneline(tstate,"NOTIFY");
158+
return;
159+
}
134160
else
161+
{
135162
do_text_output_oneline(tstate,"UTILITY");
136-
return;
163+
return;
164+
}
137165
}
138166

139-
/*
140-
* We don't support DECLARE CURSOR in EXPLAIN, but parser will take it
141-
* because it's an OptimizableStmt
142-
*/
143-
if (query->isPortal)
144-
elog(ERROR,"EXPLAIN / DECLARE CURSOR is not supported");
145-
146167
/* plan the query */
147-
plan=planner(query);
148-
149-
/* pg_plan could have failed */
150-
if (plan==NULL)
151-
return;
168+
plan=planner(query,isCursor,cursorOptions);
152169

153170
/* Create a QueryDesc requesting no output */
154171
queryDesc=CreateQueryDesc(query,plan,None,NULL,NULL,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp