Re: Problem while updating a foreign table pointing to a partitioned table on foreign server

Hello.

At Mon, 04 Jun 2018 20:58:28 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20180604(dot)205828(dot)208262556(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
> It fails on some join-pushdown cases since it doesn't add tid
> columns to join tlist. I suppose that build_tlist_to_deparse
> needs something but I'll consider further tomorrow.

I made it work with a few exceptions and bumped. PARAM_EXEC
doesn't work at all in a case where Sort exists between
ForeignUpdate and ForeignScan.

=====
explain (verbose, costs off)
update bar set f2 = f2 + 100
from
( select f1 from foo union all select f1+3 from foo ) ss
where bar.f1 = ss.f1;
QUERY PLAN
-----------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE tableoid = $1 AND ctid = $2
...
-> Merge Join
Output: bar2.f1, (bar2.f2 + 100), bar2.f3, (ROW(foo.f1))
Merge Cond: (bar2.f1 = foo.f1)
-> Sort
Output: bar2.f1, bar2.f2, bar2.f3, bar2.tableoid, bar2.ctid
Sort Key: bar2.f1
-> Foreign Scan on public.bar2
Output: bar2.f1, bar2.f2, bar2.f3, bar2.tableoid, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE
=====

Even if this worked fine, it cannot be back-patched. We need an
extra storage moves together with tuples or prevent sorts or
something like from being inserted there.

At Fri, 1 Jun 2018 10:21:39 -0400, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote in <CAFjFpRdraYcQnD4tKzNuP1uP6L-gnizi4HLU_UA=28Q2M4zoDA(at)mail(dot)gmail(dot)com>
> I am not suggesting to commit 0003 in my patch set, but just 0001 and
> 0002 which just raise an error when multiple rows get updated when
> only one row is expected to be updated.

So I agree to commit the two at least in order to prevent doing
wrong silently.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center