Skip to content

Commit 10bad8c

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 d2267b6 commit 10bad8c

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
@@ -2210,8 +2210,6 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
22102210
/* If not supplied by input plans, evaluate the contained expr */
22112211
return fix_join_expr_mutator((Node *) phv->phexpr, context);
22122212
}
2213-
if (IsA(node, Param))
2214-
return fix_param_node(context->root, (Param *) node);
22152213
/* Try matching more complex expressions too, if tlists have any */
22162214
if (context->outer_itlist && context->outer_itlist->has_non_vars)
22172215
{
@@ -2229,6 +2227,9 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
22292227
if (newvar)
22302228
return (Node *) newvar;
22312229
}
2230+
/* Special cases (apply only AFTER failing to match to lower tlist) */
2231+
if (IsA(node, Param))
2232+
return fix_param_node(context->root, (Param *) node);
22322233
fix_expr_common(context->root, node);
22332234
return expression_tree_mutator(node,
22342235
fix_join_expr_mutator,
@@ -2316,6 +2317,16 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
23162317
/* If not supplied by input plan, evaluate the contained expr */
23172318
return fix_upper_expr_mutator((Node *) phv->phexpr, context);
23182319
}
2320+
/* Try matching more complex expressions too, if tlist has any */
2321+
if (context->subplan_itlist->has_non_vars)
2322+
{
2323+
newvar = search_indexed_tlist_for_non_var(node,
2324+
context->subplan_itlist,
2325+
context->newvarno);
2326+
if (newvar)
2327+
return (Node *) newvar;
2328+
}
2329+
/* Special cases (apply only AFTER failing to match to lower tlist) */
23192330
if (IsA(node, Param))
23202331
return fix_param_node(context->root, (Param *) node);
23212332
if (IsA(node, Aggref))
@@ -2340,15 +2351,6 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
23402351
}
23412352
/* If no match, just fall through to process it normally */
23422353
}
2343-
/* Try matching more complex expressions too, if tlist has any */
2344-
if (context->subplan_itlist->has_non_vars)
2345-
{
2346-
newvar = search_indexed_tlist_for_non_var(node,
2347-
context->subplan_itlist,
2348-
context->newvarno);
2349-
if (newvar)
2350-
return (Node *) newvar;
2351-
}
23522354
fix_expr_common(context->root, node);
23532355
return expression_tree_mutator(node,
23542356
fix_upper_expr_mutator,

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

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

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

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

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

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

0 commit comments

Comments
 (0)