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

Commit159efe4

Browse files
committed
Fix misbehavior of CTE-used-in-a-subplan during EPQ rechecks.
An updating query that reads a CTE within an InitPlan or SubPlan could getincorrect results if it updates rows that are concurrently being modified.This is caused by CteScanNext supposing that nothing inside its recursiveExecProcNode call could change which read pointer is selected in the CTE'sshared tuplestore. While that's normally true because of scopingconsiderations, it can break down if an EPQ plan tree gets built during thecall, because EvalPlanQualStart builds execution trees for all subplanswhether they're going to be used during the recheck or not. And it seemslike a pretty shaky assumption anyway, so let's just reselect our own readpointer here.Per bug #14870 from Andrei Gorita. This has been broken since CTEs wereimplemented, so back-patch to all supported branches.Discussion:https://postgr.es/m/20171024155358.1471.82377@wrigleys.postgresql.org
1 parent4108a28 commit159efe4

File tree

3 files changed

+37
-0
lines changed

3 files changed

+37
-0
lines changed

‎src/backend/executor/nodeCtescan.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -107,6 +107,13 @@ CteScanNext(CteScanState *node)
107107
returnNULL;
108108
}
109109

110+
/*
111+
* There are corner cases where the subplan could change which
112+
* tuplestore read pointer is active, so be sure to reselect ours
113+
* before storing the tuple we got.
114+
*/
115+
tuplestore_select_read_pointer(tuplestorestate,node->readptr);
116+
110117
/*
111118
* Append a copy of the returned tuple to tuplestore. NOTE: because
112119
* our read pointer is certainly in EOF state, its read position will
@@ -178,6 +185,12 @@ ExecInitCteScan(CteScan *node, EState *estate, int eflags)
178185
* we might be asked to rescan the CTE even though upper levels didn't
179186
* tell us to be prepared to do it efficiently. Annoying, since this
180187
* prevents truncation of the tuplestore. XXX FIXME
188+
*
189+
* Note: if we are in an EPQ recheck plan tree, it's likely that no access
190+
* to the tuplestore is needed at all, making this even more annoying.
191+
* It's not worth improving that as long as all the read pointers would
192+
* have REWIND anyway, but if we ever improve this logic then that aspect
193+
* should be considered too.
181194
*/
182195
eflags |=EXEC_FLAG_REWIND;
183196

‎src/test/isolation/expected/eval-plan-qual.out

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -217,3 +217,18 @@ id data id data
217217
9 0 9 0
218218
10 0 10 0
219219
step c1: COMMIT;
220+
221+
starting permutation: wrtwcte multireadwcte c1 c2
222+
step wrtwcte: UPDATE table_a SET value = 'tableAValue2' WHERE id = 1;
223+
step multireadwcte:
224+
WITH updated AS (
225+
UPDATE table_a SET value = 'tableAValue3' WHERE id = 1 RETURNING id
226+
)
227+
SELECT (SELECT id FROM updated) AS subid, * FROM updated;
228+
<waiting ...>
229+
step c1: COMMIT;
230+
step c2: COMMIT;
231+
step multireadwcte: <... completed>
232+
subid id
233+
234+
1 1

‎src/test/isolation/specs/eval-plan-qual.spec

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -139,6 +139,14 @@ step "readwcte"{
139139
SELECT*FROMcte2;
140140
}
141141

142+
# this test exercises a different CTE misbehavior, cf bug #14870
143+
step"multireadwcte"{
144+
WITHupdatedAS (
145+
UPDATEtable_aSETvalue='tableAValue3'WHEREid=1RETURNINGid
146+
)
147+
SELECT (SELECTidFROMupdated)ASsubid,*FROMupdated;
148+
}
149+
142150
teardown{COMMIT; }
143151

144152
permutation"wx1""wx2""c1""c2""read"
@@ -151,3 +159,4 @@ permutation "wx2" "lockwithvalues" "c2" "c1" "read"
151159
permutation"updateforss""readforss""c1""c2"
152160
permutation"wrtwcte""readwcte""c1""c2"
153161
permutation"wrjt""selectjoinforupdate""c2""c1"
162+
permutation"wrtwcte""multireadwcte""c1""c2"

0 commit comments

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