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

Commit048efc2

Browse files
committed
Disallow scrolling of FOR UPDATE/FOR SHARE cursors, so as to avoid problems
in corner cases such as re-fetching a just-deleted row. We may be able torelax this someday, but let's find out how many people really care beforewe invest a lot of work in it. Per report from Heikki and subsequentdiscussion.While in the neighborhood, make the combination of INSENSITIVE and FOR UPDATEthrow an error, since they are semantically incompatible. (Up to now we'veaccepted but just ignored the INSENSITIVE option of DECLARE CURSOR.)
1 parent8a35b07 commit048efc2

File tree

7 files changed

+62
-35
lines changed

7 files changed

+62
-35
lines changed

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

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.41 2007/06/11 01:16:21 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.42 2007/10/24 23:27:07 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -172,7 +172,7 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITI
172172
transaction. Thus, <command>DECLARE</> without <literal>WITH
173173
HOLD</literal> is useless outside a transaction block: the cursor would
174174
survive only to the completion of the statement. Therefore
175-
<productname>PostgreSQL</productname> reports an error ifthis
175+
<productname>PostgreSQL</productname> reports an error ifsuch a
176176
command is used outside a transaction block.
177177
Use
178178
<xref linkend="sql-begin" endterm="sql-begin-title">,
@@ -230,6 +230,11 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITI
230230
will have no effect if the row was changed meanwhile.
231231
</para>
232232

233+
<para>
234+
<literal>SCROLL</literal> may not be specified when the query
235+
includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>.
236+
</para>
237+
233238
<para>
234239
The SQL standard only makes provisions for cursors in embedded
235240
<acronym>SQL</acronym>. The <productname>PostgreSQL</productname>
@@ -265,10 +270,11 @@ DECLARE liahona CURSOR FOR SELECT * FROM films;
265270
<title>Compatibility</title>
266271

267272
<para>
268-
The SQL standardspecifies thatby default, cursors are sensitive to
269-
concurrent updates of the underlying data. In
273+
The SQL standardsays thatit is implementation-dependent whether cursors
274+
are sensitive toconcurrent updates of the underlying data by default. In
270275
<productname>PostgreSQL</productname>, cursors are insensitive by default,
271-
and can be made sensitive by specifying <literal>FOR UPDATE</>.
276+
and can be made sensitive by specifying <literal>FOR UPDATE</>. Other
277+
products may work differently.
272278
</para>
273279

274280
<para>

‎src/backend/commands/portalcmds.c

Lines changed: 4 additions & 3 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.65 2007/04/27 22:05:47 tgl Exp $
17+
* $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.66 2007/10/24 23:27:08 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -102,12 +102,13 @@ PerformCursorOpen(PlannedStmt *stmt, ParamListInfo params,
102102
*
103103
* If the user didn't specify a SCROLL type, allow or disallow scrolling
104104
* based on whether it would require any additional runtime overhead to do
105-
* so.
105+
* so. Also, we disallow scrolling for FOR UPDATE cursors.
106106
*/
107107
portal->cursorOptions=cstmt->options;
108108
if (!(portal->cursorOptions& (CURSOR_OPT_SCROLL |CURSOR_OPT_NO_SCROLL)))
109109
{
110-
if (ExecSupportsBackwardScan(stmt->planTree))
110+
if (stmt->rowMarks==NIL&&
111+
ExecSupportsBackwardScan(stmt->planTree))
111112
portal->cursorOptions |=CURSOR_OPT_SCROLL;
112113
else
113114
portal->cursorOptions |=CURSOR_OPT_NO_SCROLL;

‎src/backend/executor/spi.c

Lines changed: 18 additions & 1 deletion
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.181 2007/09/20 17:56:31 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.182 2007/10/24 23:27:08 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -975,12 +975,29 @@ SPI_cursor_open(const char *name, SPIPlanPtr plan,
975975
{
976976
if (list_length(stmt_list)==1&&
977977
IsA((Node*)linitial(stmt_list),PlannedStmt)&&
978+
((PlannedStmt*)linitial(stmt_list))->rowMarks==NIL&&
978979
ExecSupportsBackwardScan(((PlannedStmt*)linitial(stmt_list))->planTree))
979980
portal->cursorOptions |=CURSOR_OPT_SCROLL;
980981
else
981982
portal->cursorOptions |=CURSOR_OPT_NO_SCROLL;
982983
}
983984

985+
/*
986+
* Disallow SCROLL with SELECT FOR UPDATE. This is not redundant with
987+
* the check in transformDeclareCursorStmt because the cursor options
988+
* might not have come through there.
989+
*/
990+
if (portal->cursorOptions&CURSOR_OPT_SCROLL)
991+
{
992+
if (list_length(stmt_list)==1&&
993+
IsA((Node*)linitial(stmt_list),PlannedStmt)&&
994+
((PlannedStmt*)linitial(stmt_list))->rowMarks!=NIL)
995+
ereport(ERROR,
996+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
997+
errmsg("DECLARE CURSOR SCROLL ... FOR UPDATE/SHARE is not supported"),
998+
errdetail("Scrollable cursors must be READ ONLY.")));
999+
}
1000+
9841001
/*
9851002
* If told to be read-only, we'd better check for read-only queries.
9861003
* This can't be done earlier because we need to look at the finished,

‎src/backend/parser/analyze.c

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
20-
*$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.367 2007/06/23 22:12:51 tgl Exp $
20+
*$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.368 2007/10/24 23:27:08 tgl Exp $
2121
*
2222
*-------------------------------------------------------------------------
2323
*/
@@ -1623,6 +1623,20 @@ transformDeclareCursorStmt(ParseState *pstate, DeclareCursorStmt *stmt)
16231623
errmsg("DECLARE CURSOR WITH HOLD ... FOR UPDATE/SHARE is not supported"),
16241624
errdetail("Holdable cursors must be READ ONLY.")));
16251625

1626+
/* FOR UPDATE and SCROLL are not compatible */
1627+
if (result->rowMarks!=NIL&& (stmt->options&CURSOR_OPT_SCROLL))
1628+
ereport(ERROR,
1629+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1630+
errmsg("DECLARE CURSOR SCROLL ... FOR UPDATE/SHARE is not supported"),
1631+
errdetail("Scrollable cursors must be READ ONLY.")));
1632+
1633+
/* FOR UPDATE and INSENSITIVE are not compatible */
1634+
if (result->rowMarks!=NIL&& (stmt->options&CURSOR_OPT_INSENSITIVE))
1635+
ereport(ERROR,
1636+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1637+
errmsg("DECLARE CURSOR INSENSITIVE ... FOR UPDATE/SHARE is not supported"),
1638+
errdetail("Insensitive cursors must be READ ONLY.")));
1639+
16261640
/* We won't need the raw querytree any more */
16271641
stmt->query=NULL;
16281642

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.353 2007/09/03 18:46:30 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.354 2007/10/24 23:27:08 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -1479,7 +1479,7 @@ typedef struct CommentStmt
14791479
#defineCURSOR_OPT_BINARY0x0001/* BINARY */
14801480
#defineCURSOR_OPT_SCROLL0x0002/* SCROLL explicitly given */
14811481
#defineCURSOR_OPT_NO_SCROLL0x0004/* NO SCROLL explicitly given */
1482-
#defineCURSOR_OPT_INSENSITIVE0x0008/* INSENSITIVE(unimplemented)*/
1482+
#defineCURSOR_OPT_INSENSITIVE0x0008/* INSENSITIVE */
14831483
#defineCURSOR_OPT_HOLD0x0010/* WITH HOLD */
14841484
#defineCURSOR_OPT_FAST_PLAN0x0020/* prefer fast-start plan */
14851485

‎src/test/regress/expected/portals.out

Lines changed: 11 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1073,40 +1073,31 @@ SELECT * FROM uctest;
10731073
23 | three
10741074
(2 rows)
10751075

1076-
-- sensitive cursor should show effects of updates or deletes
1077-
-- XXX current behavior is WRONG
1078-
FETCH RELATIVE 0 FROM c1;
1076+
DELETE FROM uctest WHERE CURRENT OF c1;
1077+
SELECT * FROM uctest;
10791078
f1 | f2
10801079
----+-----
10811080
8 | one
10821081
(1 row)
10831082

1084-
DELETE FROM uctest WHERE CURRENT OF c1;
1085-
SELECT * FROM uctest;
1086-
f1 | f2
1087-
----+-------
1088-
23 | three
1089-
(1 row)
1090-
10911083
DELETE FROM uctest WHERE CURRENT OF c1; -- no-op
10921084
SELECT * FROM uctest;
1093-
f1 | f2
1094-
----+-------
1095-
23 | three
1085+
f1 |f2
1086+
----+-----
1087+
8 | one
10961088
(1 row)
10971089

10981090
UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op
10991091
SELECT * FROM uctest;
1100-
f1 | f2
1101-
----+-------
1102-
23 | three
1092+
f1 |f2
1093+
----+-----
1094+
8 | one
11031095
(1 row)
11041096

1097+
--- sensitive cursors can't currently scroll back, so this is an error:
11051098
FETCH RELATIVE 0 FROM c1;
1106-
f1 | f2
1107-
----+----
1108-
(0 rows)
1109-
1099+
ERROR: cursor can only scan forward
1100+
HINT: Declare it with SCROLL option to enable backward scan.
11101101
ROLLBACK;
11111102
SELECT * FROM uctest;
11121103
f1 | f2

‎src/test/regress/sql/portals.sql

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -376,15 +376,13 @@ UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
376376
SELECT*FROM uctest;
377377
UPDATE uctestSET f1= f1+10WHERE CURRENT OF c1;
378378
SELECT*FROM uctest;
379-
-- sensitive cursor should show effects of updates or deletes
380-
-- XXX current behavior is WRONG
381-
FETCH RELATIVE0FROM c1;
382379
DELETEFROM uctestWHERE CURRENT OF c1;
383380
SELECT*FROM uctest;
384381
DELETEFROM uctestWHERE CURRENT OF c1;-- no-op
385382
SELECT*FROM uctest;
386383
UPDATE uctestSET f1= f1+10WHERE CURRENT OF c1;-- no-op
387384
SELECT*FROM uctest;
385+
--- sensitive cursors can't currently scroll back, so this is an error:
388386
FETCH RELATIVE0FROM c1;
389387
ROLLBACK;
390388
SELECT*FROM uctest;

0 commit comments

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