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

Commit215824f

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 parent38a9573 commit215824f

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
@@ -390,6 +390,22 @@ foreign_expr_walker(Node *node,
390390
{
391391
Param*p= (Param*)node;
392392

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

‎contrib/postgres_fdw/expected/postgres_fdw.out

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

54515451
DELETE FROM ft2 WHERE ft2.c1 > 1200;
5452+
-- Test UPDATE with a MULTIEXPR sub-select
5453+
-- (maybe someday this'll be remotely executable, but not today)
5454+
EXPLAIN (verbose, costs off)
5455+
UPDATE ft2 AS target SET (c2, c7) = (
5456+
SELECT c2 * 10, c7
5457+
FROM ft2 AS src
5458+
WHERE target.c1 = src.c1
5459+
) WHERE c1 > 1100;
5460+
QUERY PLAN
5461+
---------------------------------------------------------------------------------------------------------------------------------------------------
5462+
Update on public.ft2 target
5463+
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1
5464+
-> Foreign Scan on public.ft2 target
5465+
Output: target.c1, $1, NULL::integer, target.c3, target.c4, target.c5, target.c6, $2, target.c8, (SubPlan 1 (returns $1,$2)), target.ctid
5466+
Remote SQL: SELECT "C 1", c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE
5467+
SubPlan 1 (returns $1,$2)
5468+
-> Foreign Scan on public.ft2 src
5469+
Output: (src.c2 * 10), src.c7
5470+
Remote SQL: SELECT c2, c7 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
5471+
(9 rows)
5472+
5473+
UPDATE ft2 AS target SET (c2, c7) = (
5474+
SELECT c2 * 10, c7
5475+
FROM ft2 AS src
5476+
WHERE target.c1 = src.c1
5477+
) WHERE c1 > 1100;
5478+
UPDATE ft2 AS target SET (c2) = (
5479+
SELECT c2 / 10
5480+
FROM ft2 AS src
5481+
WHERE target.c1 = src.c1
5482+
) WHERE c1 > 1100;
54525483
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
54535484
-- user-defined operators/functions
54545485
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
@@ -1191,6 +1191,26 @@ DELETE FROM ft2
11911191
RETURNING100;
11921192
DELETEFROM ft2WHEREft2.c1>1200;
11931193

1194+
-- Test UPDATE with a MULTIEXPR sub-select
1195+
-- (maybe someday this'll be remotely executable, but not today)
1196+
EXPLAIN (verbose, costs off)
1197+
UPDATE ft2AS targetSET (c2, c7)= (
1198+
SELECT c2*10, c7
1199+
FROM ft2AS src
1200+
WHEREtarget.c1=src.c1
1201+
)WHERE c1>1100;
1202+
UPDATE ft2AS targetSET (c2, c7)= (
1203+
SELECT c2*10, c7
1204+
FROM ft2AS src
1205+
WHEREtarget.c1=src.c1
1206+
)WHERE c1>1100;
1207+
1208+
UPDATE ft2AS targetSET (c2)= (
1209+
SELECT c2/10
1210+
FROM ft2AS src
1211+
WHEREtarget.c1=src.c1
1212+
)WHERE c1>1100;
1213+
11941214
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
11951215
-- user-defined operators/functions
11961216
ALTER SERVER loopback OPTIONS (DROP extensions);

0 commit comments

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