Skip to content

Commit 302d4ee

Browse files
tglsfdctglsfdc
committed
Repair bogus handling of multi-assignment Params in upper plan levels.
Our support for multiple-set-clauses in UPDATE assumes that the Params referencing a MULTIEXPR_SUBLINK SubPlan will appear before that SubPlan in the targetlist of the plan node that calculates the updated row. (Yeah, it's a hack...) In some PG branches it's possible that a Result node gets inserted between the primary calculation of the update tlist and the ModifyTable node. setrefs.c did the wrong thing in this case and left the upper-level Params as Params, causing a crash at runtime. What it should do is replace them with "outer" Vars referencing the child plan node's output. That's a result of careless ordering of operations in fix_upper_expr_mutator, so we can fix it just by reordering the code. Fix fix_join_expr_mutator similarly for consistency, even though join nodes could never appear in such a context. (In general, it seems likely to be a bit cheaper to use Vars than Params in such situations anyway, so this patch might offer a tiny performance improvement.) The hazard extends back to 9.5 where the MULTIEXPR_SUBLINK stuff was introduced, so back-patch that far. However, this may be a live bug only in 9.6.x and 10.x, as the other branches don't seem to want to calculate the final tlist below the Result node. (That plan shape change between branches might be a mini-bug in itself, but I'm not really interested in digging into the reasons for that right now. Still, add a regression test memorializing what we expect there, so we'll notice if it changes again.) Per bug report from Eduards Bezverhijs. Discussion: https://postgr.es/m/b6cd572a-3e44-8785-75e9-c512a5a17a73@tieto.com
1 parent 4e33da5 commit 302d4ee

File tree

3 files changed

+54
-11
lines changed

3 files changed

+54
-11
lines changed

‎src/backend/optimizer/plan/setrefs.c

Lines changed: 13 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -2312,8 +2312,6 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
23122312
/* If not supplied by input plans, evaluate the contained expr */
23132313
return fix_join_expr_mutator((Node *) phv->phexpr, context);
23142314
}
2315-
if (IsA(node, Param))
2316-
return fix_param_node(context->root, (Param *) node);
23172315
/* Try matching more complex expressions too, if tlists have any */
23182316
if (context->outer_itlist && context->outer_itlist->has_non_vars)
23192317
{
@@ -2331,6 +2329,9 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
23312329
if (newvar)
23322330
return (Node *) newvar;
23332331
}
2332+
/* Special cases (apply only AFTER failing to match to lower tlist) */
2333+
if (IsA(node, Param))
2334+
return fix_param_node(context->root, (Param *) node);
23342335
fix_expr_common(context->root, node);
23352336
return expression_tree_mutator(node,
23362337
fix_join_expr_mutator,
@@ -2418,6 +2419,16 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
24182419
/* If not supplied by input plan, evaluate the contained expr */
24192420
return fix_upper_expr_mutator((Node *) phv->phexpr, context);
24202421
}
2422+
/* Try matching more complex expressions too, if tlist has any */
2423+
if (context->subplan_itlist->has_non_vars)
2424+
{
2425+
newvar = search_indexed_tlist_for_non_var((Expr *) node,
2426+
context->subplan_itlist,
2427+
context->newvarno);
2428+
if (newvar)
2429+
return (Node *) newvar;
2430+
}
2431+
/* Special cases (apply only AFTER failing to match to lower tlist) */
24212432
if (IsA(node, Param))
24222433
return fix_param_node(context->root, (Param *) node);
24232434
if (IsA(node, Aggref))
@@ -2442,15 +2453,6 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
24422453
}
24432454
/* If no match, just fall through to process it normally */
24442455
}
2445-
/* Try matching more complex expressions too, if tlist has any */
2446-
if (context->subplan_itlist->has_non_vars)
2447-
{
2448-
newvar = search_indexed_tlist_for_non_var((Expr *) node,
2449-
context->subplan_itlist,
2450-
context->newvarno);
2451-
if (newvar)
2452-
return (Node *) newvar;
2453-
}
24542456
fix_expr_common(context->root, node);
24552457
return expression_tree_mutator(node,
24562458
fix_upper_expr_mutator,

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

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -167,6 +167,37 @@ SELECT a, b, char_length(c) FROM update_test;
167167
42 | 12 | 10000
168168
(4 rows)
169169

170+
-- Check multi-assignment with a Result node to handle a one-time filter.
171+
EXPLAIN (VERBOSE, COSTS OFF)
172+
UPDATE update_test t
173+
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
174+
WHERE CURRENT_USER = SESSION_USER;
175+
QUERY PLAN
176+
------------------------------------------------------------------
177+
Update on public.update_test t
178+
-> Result
179+
Output: $1, $2, t.c, (SubPlan 1 (returns $1,$2)), t.ctid
180+
One-Time Filter: (CURRENT_USER = SESSION_USER)
181+
-> Seq Scan on public.update_test t
182+
Output: t.c, t.a, t.ctid
183+
SubPlan 1 (returns $1,$2)
184+
-> Seq Scan on public.update_test s
185+
Output: s.b, s.a
186+
Filter: (s.a = t.a)
187+
(10 rows)
188+
189+
UPDATE update_test t
190+
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
191+
WHERE CURRENT_USER = SESSION_USER;
192+
SELECT a, b, char_length(c) FROM update_test;
193+
a | b | char_length
194+
-----+----+-------------
195+
| |
196+
100 | 21 |
197+
12 | 41 | 10000
198+
12 | 42 | 10000
199+
(4 rows)
200+
170201
-- Test ON CONFLICT DO UPDATE
171202
INSERT INTO upsert_test VALUES(1, 'Boo');
172203
-- uncorrelated sub-select:

‎src/test/regress/sql/update.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,16 @@ UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
8989
UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
9090
SELECT a, b, char_length(c) FROM update_test;
9191

92+
-- Check multi-assignment with a Result node to handle a one-time filter.
93+
EXPLAIN (VERBOSE, COSTS OFF)
94+
UPDATE update_test t
95+
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
96+
WHERE CURRENT_USER = SESSION_USER;
97+
UPDATE update_test t
98+
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
99+
WHERE CURRENT_USER = SESSION_USER;
100+
SELECT a, b, char_length(c) FROM update_test;
101+
92102
-- Test ON CONFLICT DO UPDATE
93103
INSERT INTO upsert_test VALUES(1, 'Boo');
94104
-- uncorrelated sub-select:

0 commit comments

Comments
 (0)