diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2020-01-26 14:31:08 -0500 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2020-01-26 14:31:08 -0500 |
| commit | 5220ced0de5de1b88437c00fffabfac223ee9866 (patch) | |
| tree | 40d2321dbc000951ec838819b3552fa101be39e7 /contrib/postgres_fdw/sql | |
| parent | b9988facaec81733ab8a67c2bd75b5d3e72c1e16 (diff) | |
| download | postgresql-5220ced0de5de1b88437c00fffabfac223ee9866.tar.gz postgresql-5220ced0de5de1b88437c00fffabfac223ee9866.zip | |
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 examined
for shippability. Currently that ends up crashing if the sub-SELECT
contains any remote Vars. Prevent the crash by deeming MULTIEXEC
Params 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 technology
would 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, so
it won't surprise anybody that this doesn't end up as a remote direct
update. I'd be inclined to see if that general limitation can be fixed
before 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 perform
remote direct updates then, so the case didn't arise anyway.
Discussion: https://postgr.es/m/CAJif3k+iA_ekBB5Zw2hDBaE1wtiQa4LH4_JUXrrMGwTrH0J01Q@mail.gmail.com
Diffstat (limited to 'contrib/postgres_fdw/sql')
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 20 |
1 files changed, 20 insertions, 0 deletions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index e659704ef07..14e9ae76cdf 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1167,6 +1167,26 @@ DELETE FROM ft2 RETURNING 100; DELETE FROM ft2 WHERE ft2.c1 > 1200; +-- Test UPDATE with a MULTIEXPR sub-select +-- (maybe someday this'll be remotely executable, but not today) +EXPLAIN (verbose, costs off) +UPDATE ft2 AS target SET (c2, c7) = ( + SELECT c2 * 10, c7 + FROM ft2 AS src + WHERE target.c1 = src.c1 +) WHERE c1 > 1100; +UPDATE ft2 AS target SET (c2, c7) = ( + SELECT c2 * 10, c7 + FROM ft2 AS src + WHERE target.c1 = src.c1 +) WHERE c1 > 1100; + +UPDATE ft2 AS target SET (c2) = ( + SELECT c2 / 10 + FROM ft2 AS src + WHERE target.c1 = src.c1 +) WHERE c1 > 1100; + -- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing -- user-defined operators/functions ALTER SERVER loopback OPTIONS (DROP extensions); |