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

Commit603e03b

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 parentb9a9cb1 commit603e03b

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
@@ -387,6 +387,22 @@ foreign_expr_walker(Node *node,
387387
{
388388
Param*p= (Param*)node;
389389

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

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5535,6 +5535,37 @@ DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
55355535
ft2
55365536
(1 row)
55375537

5538+
-- Test UPDATE with a MULTIEXPR sub-select
5539+
-- (maybe someday this'll be remotely executable, but not today)
5540+
EXPLAIN (verbose, costs off)
5541+
UPDATE ft2 AS target SET (c2, c7) = (
5542+
SELECT c2 * 10, c7
5543+
FROM ft2 AS src
5544+
WHERE target.c1 = src.c1
5545+
) WHERE c1 > 1100;
5546+
QUERY PLAN
5547+
---------------------------------------------------------------------------------------------------------------------------------------------------
5548+
Update on public.ft2 target
5549+
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1
5550+
-> Foreign Scan on public.ft2 target
5551+
Output: target.c1, $1, NULL::integer, target.c3, target.c4, target.c5, target.c6, $2, target.c8, (SubPlan 1 (returns $1,$2)), target.ctid
5552+
Remote SQL: SELECT "C 1", c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE
5553+
SubPlan 1 (returns $1,$2)
5554+
-> Foreign Scan on public.ft2 src
5555+
Output: (src.c2 * 10), src.c7
5556+
Remote SQL: SELECT c2, c7 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
5557+
(9 rows)
5558+
5559+
UPDATE ft2 AS target SET (c2, c7) = (
5560+
SELECT c2 * 10, c7
5561+
FROM ft2 AS src
5562+
WHERE target.c1 = src.c1
5563+
) WHERE c1 > 1100;
5564+
UPDATE ft2 AS target SET (c2) = (
5565+
SELECT c2 / 10
5566+
FROM ft2 AS src
5567+
WHERE target.c1 = src.c1
5568+
) WHERE c1 > 1100;
55385569
-- Test that trigger on remote table works as expected
55395570
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
55405571
BEGIN

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1138,6 +1138,26 @@ EXPLAIN (verbose, costs off)
11381138
DELETEFROM ft2WHERE c1=9999 RETURNING tableoid::regclass;-- can be pushed down
11391139
DELETEFROM ft2WHERE c1=9999 RETURNING tableoid::regclass;
11401140

1141+
-- Test UPDATE with a MULTIEXPR sub-select
1142+
-- (maybe someday this'll be remotely executable, but not today)
1143+
EXPLAIN (verbose, costs off)
1144+
UPDATE ft2AS targetSET (c2, c7)= (
1145+
SELECT c2*10, c7
1146+
FROM ft2AS src
1147+
WHEREtarget.c1=src.c1
1148+
)WHERE c1>1100;
1149+
UPDATE ft2AS targetSET (c2, c7)= (
1150+
SELECT c2*10, c7
1151+
FROM ft2AS src
1152+
WHEREtarget.c1=src.c1
1153+
)WHERE c1>1100;
1154+
1155+
UPDATE ft2AS targetSET (c2)= (
1156+
SELECT c2/10
1157+
FROM ft2AS src
1158+
WHEREtarget.c1=src.c1
1159+
)WHERE c1>1100;
1160+
11411161
-- Test that trigger on remote table works as expected
11421162
CREATEOR REPLACE FUNCTION"S 1".F_BRTRIG() RETURNS triggerAS $$
11431163
BEGIN

0 commit comments

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