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

Commit5220ced

Browse files
committed
In postgres_fdw, don't try to ship MULTIEXPR updates to remote server.
In a statement like "UPDATE remote_tab SET (x,y) = (SELECT ...)",we'd conclude that the statement could be directly executed remotely,because the sub-SELECT is in a resjunk tlist item that's not examinedfor shippability. Currently that ends up crashing if the sub-SELECTcontains any remote Vars. Prevent the crash by deeming MULTIEXECParams to be unshippable.This is a bit of a brute-force solution, since if the sub-SELECT*doesn't* contain any remote Vars, the current execution technologywould work; but that's not a terribly common use-case for this syntax,I think. In any case, we generally don't try to ship sub-SELECTs, soit won't surprise anybody that this doesn't end up as a remote directupdate. I'd be inclined to see if that general limitation can be fixedbefore worrying about this case further.Per report from Lukáš Sobotka.Back-patch to 9.6. 9.5 had MULTIEXPR, but we didn't try to performremote direct updates then, so the case didn't arise anyway.Discussion:https://postgr.es/m/CAJif3k+iA_ekBB5Zw2hDBaE1wtiQa4LH4_JUXrrMGwTrH0J01Q@mail.gmail.com
1 parentb9988fa commit5220ced

File tree

3 files changed

+67
-0
lines changed

3 files changed

+67
-0
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -391,6 +391,22 @@ foreign_expr_walker(Node *node,
391391
{
392392
Param*p= (Param*)node;
393393

394+
/*
395+
* If it's a MULTIEXPR Param, punt. We can't tell from here
396+
* whether the referenced sublink/subplan contains any remote
397+
* Vars; if it does, handling that is too complicated to
398+
* consider supporting at present. Fortunately, MULTIEXPR
399+
* Params are not reduced to plain PARAM_EXEC until the end of
400+
* planning, so we can easily detect this case. (Normal
401+
* PARAM_EXEC Params are safe to ship because their values
402+
* come from somewhere else in the plan tree; but a MULTIEXPR
403+
* references a sub-select elsewhere in the same targetlist,
404+
* so we'd be on the hook to evaluate it somehow if we wanted
405+
* to handle such cases as direct foreign updates.)
406+
*/
407+
if (p->paramkind==PARAM_MULTIEXPR)
408+
return false;
409+
394410
/*
395411
* Collation rule is same as for Consts and non-foreign Vars.
396412
*/

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5594,6 +5594,37 @@ DELETE FROM ft2
55945594
(10 rows)
55955595

55965596
DELETE FROM ft2 WHERE ft2.c1 > 1200;
5597+
-- Test UPDATE with a MULTIEXPR sub-select
5598+
-- (maybe someday this'll be remotely executable, but not today)
5599+
EXPLAIN (verbose, costs off)
5600+
UPDATE ft2 AS target SET (c2, c7) = (
5601+
SELECT c2 * 10, c7
5602+
FROM ft2 AS src
5603+
WHERE target.c1 = src.c1
5604+
) WHERE c1 > 1100;
5605+
QUERY PLAN
5606+
---------------------------------------------------------------------------------------------------------------------------------------------------
5607+
Update on public.ft2 target
5608+
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1
5609+
-> Foreign Scan on public.ft2 target
5610+
Output: target.c1, $1, NULL::integer, target.c3, target.c4, target.c5, target.c6, $2, target.c8, (SubPlan 1 (returns $1,$2)), target.ctid
5611+
Remote SQL: SELECT "C 1", c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE
5612+
SubPlan 1 (returns $1,$2)
5613+
-> Foreign Scan on public.ft2 src
5614+
Output: (src.c2 * 10), src.c7
5615+
Remote SQL: SELECT c2, c7 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
5616+
(9 rows)
5617+
5618+
UPDATE ft2 AS target SET (c2, c7) = (
5619+
SELECT c2 * 10, c7
5620+
FROM ft2 AS src
5621+
WHERE target.c1 = src.c1
5622+
) WHERE c1 > 1100;
5623+
UPDATE ft2 AS target SET (c2) = (
5624+
SELECT c2 / 10
5625+
FROM ft2 AS src
5626+
WHERE target.c1 = src.c1
5627+
) WHERE c1 > 1100;
55975628
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
55985629
-- user-defined operators/functions
55995630
ALTER SERVER loopback OPTIONS (DROP extensions);

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1167,6 +1167,26 @@ DELETE FROM ft2
11671167
RETURNING100;
11681168
DELETEFROM ft2WHEREft2.c1>1200;
11691169

1170+
-- Test UPDATE with a MULTIEXPR sub-select
1171+
-- (maybe someday this'll be remotely executable, but not today)
1172+
EXPLAIN (verbose, costs off)
1173+
UPDATE ft2AS targetSET (c2, c7)= (
1174+
SELECT c2*10, c7
1175+
FROM ft2AS src
1176+
WHEREtarget.c1=src.c1
1177+
)WHERE c1>1100;
1178+
UPDATE ft2AS targetSET (c2, c7)= (
1179+
SELECT c2*10, c7
1180+
FROM ft2AS src
1181+
WHEREtarget.c1=src.c1
1182+
)WHERE c1>1100;
1183+
1184+
UPDATE ft2AS targetSET (c2)= (
1185+
SELECT c2/10
1186+
FROM ft2AS src
1187+
WHEREtarget.c1=src.c1
1188+
)WHERE c1>1100;
1189+
11701190
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
11711191
-- user-defined operators/functions
11721192
ALTER SERVER loopback OPTIONS (DROP extensions);

0 commit comments

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