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

Commit61e5328

Browse files
committed
Make FOR UPDATE/SHARE in the primary query not propagate into WITH queries;
for example in WITH w AS (SELECT * FROM foo) SELECT * FROM w, bar ... FOR UPDATEthe FOR UPDATE will now affect bar but not foo. This is more useful andconsistent than the original 8.4 behavior, which tried to propagate FOR UPDATEinto the WITH query but always failed due to assorted implementationrestrictions. Even though we are in process of removing those restrictions,it seems correct on philosophical grounds to not let the outer query'sFOR UPDATE affect the WITH query.In passing, fix isLockedRel which frequently got things wrong innested-subquery cases: "FOR UPDATE OF foo" applies to an alias foo in thecurrent query level, not subqueries. This has been broken for a long time,but it doesn't seem worth back-patching further than 8.4 because the actualconsequences are minimal. At worst the parser would sometimes getRowShareLock on a relation when it should be AccessShareLock or vice versa.That would only make a difference if someone were using ExclusiveLockconcurrently, which no standard operation does, and anyway FOR UPDATEdoesn't result in visible changes so it's not clear that the someone wouldnotice any problem. Between that and the fact that FOR UPDATE barely workswith subqueries at all in existing releases, I'm not excited about worryingabout it.
1 parentb091b97 commit61e5328

File tree

10 files changed

+65
-121
lines changed

10 files changed

+65
-121
lines changed

‎doc/src/sgml/ref/select.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/select.sgml,v 1.126 2009/10/21 20:22:38 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.127 2009/10/27 17:11:18 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -1127,6 +1127,11 @@ FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
11271127
If <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> is
11281128
applied to a view or sub-query, it affects all tables used in
11291129
the view or sub-query.
1130+
However, <literal>FOR UPDATE</literal>/<literal>FOR SHARE</literal>
1131+
do not apply to <literal>WITH</> queries referenced by the primary query.
1132+
If you want row locking to occur within a <literal>WITH</> query, specify
1133+
<literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> within the
1134+
<literal>WITH</> query.
11301135
</para>
11311136

11321137
<para>

‎src/backend/parser/analyze.c‎

Lines changed: 11 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717
* Portions Copyright (c) 1996-2009, 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.393 2009/10/26 02:26:35 tgl Exp $
20+
*$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.394 2009/10/27 17:11:18 tgl Exp $
2121
*
2222
*-------------------------------------------------------------------------
2323
*/
@@ -138,12 +138,14 @@ parse_analyze_varparams(Node *parseTree, const char *sourceText,
138138
*/
139139
Query*
140140
parse_sub_analyze(Node*parseTree,ParseState*parentParseState,
141-
CommonTableExpr*parentCTE)
141+
CommonTableExpr*parentCTE,
142+
boollocked_from_parent)
142143
{
143144
ParseState*pstate=make_parsestate(parentParseState);
144145
Query*query;
145146

146147
pstate->p_parent_cte=parentCTE;
148+
pstate->p_locked_from_parent=locked_from_parent;
147149

148150
query=transformStmt(pstate,parseTree);
149151

@@ -1424,7 +1426,7 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
14241426
* of this sub-query, because they are not in the toplevel pstate's
14251427
* namespace list.
14261428
*/
1427-
selectQuery=parse_sub_analyze((Node*)stmt,pstate,NULL);
1429+
selectQuery=parse_sub_analyze((Node*)stmt,pstate,NULL, false);
14281430

14291431
/*
14301432
* Check for bogus references to Vars on the current query level (but
@@ -2051,7 +2053,7 @@ CheckSelectLocking(Query *qry)
20512053
* This basically involves replacing names by integer relids.
20522054
*
20532055
* NB: if you need to change this, see also markQueryForLocking()
2054-
* in rewriteHandler.c, andisLockedRel() in parse_relation.c.
2056+
* in rewriteHandler.c, andisLockedRefname() in parse_relation.c.
20552057
*/
20562058
staticvoid
20572059
transformLockingClause(ParseState*pstate,Query*qry,LockingClause*lc)
@@ -2093,32 +2095,8 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc)
20932095
*/
20942096
transformLockingClause(pstate,rte->subquery,allrels);
20952097
break;
2096-
caseRTE_CTE:
2097-
{
2098-
/*
2099-
* We allow FOR UPDATE/SHARE of a WITH query to be
2100-
* propagated into the WITH, but it doesn't seem very
2101-
* sane to allow this for a reference to an
2102-
* outer-level WITH. And it definitely wouldn't work
2103-
* for a self-reference, since we're not done
2104-
* analyzing the CTE anyway.
2105-
*/
2106-
CommonTableExpr*cte;
2107-
2108-
if (rte->ctelevelsup>0||rte->self_reference)
2109-
ereport(ERROR,
2110-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2111-
errmsg("SELECT FOR UPDATE/SHARE cannot be applied to an outer-level WITH query")));
2112-
cte=GetCTEForRTE(pstate,rte,-1);
2113-
/* should be analyzed by now */
2114-
Assert(IsA(cte->ctequery,Query));
2115-
transformLockingClause(pstate,
2116-
(Query*)cte->ctequery,
2117-
allrels);
2118-
}
2119-
break;
21202098
default:
2121-
/* ignore JOIN, SPECIAL, FUNCTION RTEs */
2099+
/* ignore JOIN, SPECIAL, FUNCTION, VALUES, CTE RTEs */
21222100
break;
21232101
}
21242102
}
@@ -2185,30 +2163,10 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc)
21852163
parser_errposition(pstate,thisrel->location)));
21862164
break;
21872165
caseRTE_CTE:
2188-
{
2189-
/*
2190-
* We allow FOR UPDATE/SHARE of a WITH query
2191-
* to be propagated into the WITH, but it
2192-
* doesn't seem very sane to allow this for a
2193-
* reference to an outer-level WITH. And it
2194-
* definitely wouldn't work for a
2195-
* self-reference, since we're not done
2196-
* analyzing the CTE anyway.
2197-
*/
2198-
CommonTableExpr*cte;
2199-
2200-
if (rte->ctelevelsup>0||rte->self_reference)
2201-
ereport(ERROR,
2202-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2203-
errmsg("SELECT FOR UPDATE/SHARE cannot be applied to an outer-level WITH query"),
2204-
parser_errposition(pstate,thisrel->location)));
2205-
cte=GetCTEForRTE(pstate,rte,-1);
2206-
/* should be analyzed by now */
2207-
Assert(IsA(cte->ctequery,Query));
2208-
transformLockingClause(pstate,
2209-
(Query*)cte->ctequery,
2210-
allrels);
2211-
}
2166+
ereport(ERROR,
2167+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2168+
errmsg("SELECT FOR UPDATE/SHARE cannot be applied to a WITH query"),
2169+
parser_errposition(pstate,thisrel->location)));
22122170
break;
22132171
default:
22142172
elog(ERROR,"unrecognized RTE type: %d",

‎src/backend/parser/parse_clause.c‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.192 2009/09/09 03:32:52 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.193 2009/10/27 17:11:18 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -479,7 +479,8 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
479479
/*
480480
* Analyze and transform the subquery.
481481
*/
482-
query=parse_sub_analyze(r->subquery,pstate,NULL);
482+
query=parse_sub_analyze(r->subquery,pstate,NULL,
483+
isLockedRefname(pstate,r->alias->aliasname));
483484

484485
/*
485486
* Check that we got something reasonable.Many of these conditions are

‎src/backend/parser/parse_cte.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_cte.c,v 2.7 2009/09/09 03:32:52 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_cte.c,v 2.8 2009/10/27 17:11:18 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -229,7 +229,7 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
229229
/* Analysis not done already */
230230
Assert(IsA(cte->ctequery,SelectStmt));
231231

232-
query=parse_sub_analyze(cte->ctequery,pstate,cte);
232+
query=parse_sub_analyze(cte->ctequery,pstate,cte, false);
233233
cte->ctequery= (Node*)query;
234234

235235
/*

‎src/backend/parser/parse_expr.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_expr.c,v 1.245 2009/10/21 20:22:38 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_expr.c,v 1.246 2009/10/27 17:11:18 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1259,7 +1259,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
12591259
returnresult;
12601260

12611261
pstate->p_hasSubLinks= true;
1262-
qtree=parse_sub_analyze(sublink->subselect,pstate,NULL);
1262+
qtree=parse_sub_analyze(sublink->subselect,pstate,NULL, false);
12631263

12641264
/*
12651265
* Check that we got something reasonable.Many of these conditions are

‎src/backend/parser/parse_relation.c‎

Lines changed: 32 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_relation.c,v 1.145 2009/10/26 02:26:35 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_relation.c,v 1.146 2009/10/27 17:11:18 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -38,7 +38,6 @@ static RangeTblEntry *scanNameSpaceForRelid(ParseState *pstate, Oid relid,
3838
intlocation);
3939
staticvoidmarkRTEForSelectPriv(ParseState*pstate,RangeTblEntry*rte,
4040
intrtindex,AttrNumbercol);
41-
staticboolisLockedRel(ParseState*pstate,char*refname);
4241
staticvoidexpandRelation(Oidrelid,Alias*eref,
4342
intrtindex,intsublevels_up,
4443
intlocation,boolinclude_dropped,
@@ -909,7 +908,7 @@ addRangeTableEntry(ParseState *pstate,
909908
* to a rel in a statement, be careful to get the right access level
910909
* depending on whether we're doing SELECT FOR UPDATE/SHARE.
911910
*/
912-
lockmode=isLockedRel(pstate,refname) ?RowShareLock :AccessShareLock;
911+
lockmode=isLockedRefname(pstate,refname) ?RowShareLock :AccessShareLock;
913912
rel=parserOpenTable(pstate,relation,lockmode);
914913
rte->relid=RelationGetRelid(rel);
915914

@@ -1454,40 +1453,46 @@ addRangeTableEntryForCTE(ParseState *pstate,
14541453
/*
14551454
* Has the specified refname been selected FOR UPDATE/FOR SHARE?
14561455
*
1457-
* Note: we pay no attention to whether it's FOR UPDATE vs FOR SHARE.
1456+
* This is used when we have not yet done transformLockingClause, but need
1457+
* to know the correct lock to take during initial opening of relations.
1458+
*
1459+
* Note: we pay no attention to whether it's FOR UPDATE vs FOR SHARE,
1460+
* since the table-level lock is the same either way.
14581461
*/
1459-
staticbool
1460-
isLockedRel(ParseState*pstate,char*refname)
1462+
bool
1463+
isLockedRefname(ParseState*pstate,constchar*refname)
14611464
{
1462-
/* Outer loop to check parent query levels as well as this one */
1463-
while (pstate!=NULL)
1465+
ListCell*l;
1466+
1467+
/*
1468+
* If we are in a subquery specified as locked FOR UPDATE/SHARE from
1469+
* parent level, then act as though there's a generic FOR UPDATE here.
1470+
*/
1471+
if (pstate->p_locked_from_parent)
1472+
return true;
1473+
1474+
foreach(l,pstate->p_locking_clause)
14641475
{
1465-
ListCell*l;
1476+
LockingClause*lc= (LockingClause*)lfirst(l);
14661477

1467-
foreach(l,pstate->p_locking_clause)
1478+
if (lc->lockedRels==NIL)
14681479
{
1469-
LockingClause*lc= (LockingClause*)lfirst(l);
1480+
/* all tables used in query */
1481+
return true;
1482+
}
1483+
else
1484+
{
1485+
/* just the named tables */
1486+
ListCell*l2;
14701487

1471-
if (lc->lockedRels==NIL)
1472-
{
1473-
/* all tables used in query */
1474-
return true;
1475-
}
1476-
else
1488+
foreach(l2,lc->lockedRels)
14771489
{
1478-
/* just the named tables */
1479-
ListCell*l2;
1480-
1481-
foreach(l2,lc->lockedRels)
1482-
{
1483-
RangeVar*thisrel= (RangeVar*)lfirst(l2);
1490+
RangeVar*thisrel= (RangeVar*)lfirst(l2);
14841491

1485-
if (strcmp(refname,thisrel->relname)==0)
1486-
return true;
1487-
}
1492+
if (strcmp(refname,thisrel->relname)==0)
1493+
return true;
14881494
}
14891495
}
1490-
pstate=pstate->parentParseState;
14911496
}
14921497
return false;
14931498
}

‎src/backend/rewrite/rewriteHandler.c‎

Lines changed: 2 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/rewrite/rewriteHandler.c,v 1.188 2009/10/26 02:26:36 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/rewrite/rewriteHandler.c,v 1.189 2009/10/27 17:11:18 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -1242,35 +1242,7 @@ markQueryForLocking(Query *qry, Node *jtnode, bool forUpdate, bool noWait)
12421242
markQueryForLocking(rte->subquery, (Node*)rte->subquery->jointree,
12431243
forUpdate,noWait);
12441244
}
1245-
elseif (rte->rtekind==RTE_CTE)
1246-
{
1247-
/*
1248-
* We allow FOR UPDATE/SHARE of a WITH query to be propagated into
1249-
* the WITH, but it doesn't seem very sane to allow this for a
1250-
* reference to an outer-level WITH (compare
1251-
* transformLockingClause). Which simplifies life here.
1252-
*/
1253-
CommonTableExpr*cte=NULL;
1254-
ListCell*lc;
1255-
1256-
if (rte->ctelevelsup>0||rte->self_reference)
1257-
ereport(ERROR,
1258-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1259-
errmsg("SELECT FOR UPDATE/SHARE cannot be applied to an outer-level WITH query")));
1260-
foreach(lc,qry->cteList)
1261-
{
1262-
cte= (CommonTableExpr*)lfirst(lc);
1263-
if (strcmp(cte->ctename,rte->ctename)==0)
1264-
break;
1265-
}
1266-
if (lc==NULL)/* shouldn't happen */
1267-
elog(ERROR,"could not find CTE \"%s\"",rte->ctename);
1268-
/* should be analyzed by now */
1269-
Assert(IsA(cte->ctequery,Query));
1270-
markQueryForLocking((Query*)cte->ctequery,
1271-
(Node*) ((Query*)cte->ctequery)->jointree,
1272-
forUpdate,noWait);
1273-
}
1245+
/* other RTE types are unaffected by FOR UPDATE */
12741246
}
12751247
elseif (IsA(jtnode,FromExpr))
12761248
{

‎src/include/parser/analyze.h‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/parser/analyze.h,v 1.41 2009/09/09 03:32:52 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/parser/analyze.h,v 1.42 2009/10/27 17:11:18 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -23,7 +23,8 @@ extern Query *parse_analyze_varparams(Node *parseTree, const char *sourceText,
2323
Oid**paramTypes,int*numParams);
2424

2525
externQuery*parse_sub_analyze(Node*parseTree,ParseState*parentParseState,
26-
CommonTableExpr*parentCTE);
26+
CommonTableExpr*parentCTE,
27+
boollocked_from_parent);
2728
externQuery*transformStmt(ParseState*pstate,Node*parseTree);
2829

2930
externboolanalyze_requires_snapshot(Node*parseTree);

‎src/include/parser/parse_node.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/parser/parse_node.h,v 1.64 2009/10/21 20:22:38 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/parser/parse_node.h,v 1.65 2009/10/27 17:11:18 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -103,6 +103,7 @@ typedef struct ParseState
103103
boolp_hasSubLinks;
104104
boolp_is_insert;
105105
boolp_is_update;
106+
boolp_locked_from_parent;
106107
Relationp_target_relation;
107108
RangeTblEntry*p_target_rangetblentry;
108109
}ParseState;

‎src/include/parser/parse_relation.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/parser/parse_relation.h,v 1.65 2009/10/21 20:22:38 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/parser/parse_relation.h,v 1.66 2009/10/27 17:11:18 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -81,6 +81,7 @@ extern RangeTblEntry *addRangeTableEntryForCTE(ParseState *pstate,
8181
Indexlevelsup,
8282
Alias*alias,
8383
boolinFromCl);
84+
externboolisLockedRefname(ParseState*pstate,constchar*refname);
8485
externvoidaddRTEtoQuery(ParseState*pstate,RangeTblEntry*rte,
8586
booladdToJoinList,
8687
booladdToRelNameSpace,booladdToVarNameSpace);

0 commit comments

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