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

On Tue, Jun 5, 2018 at 3:40 PM, Kyotaro HORIGUCHI
<horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> 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
> =====

What's the problem that you faced?

>
> 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.

I think your approach still has the same problem that it's abusing the
tableOid field in the heap tuple to store tableoid from the remote as
well as local table. That's what Robert and Tom objected to [1], [2]

>
>
> 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.

I haven't heard any committer's opinion on this one yet.

[1] https://www.postgresql.org/message-id/CA+TgmobUHHZiDR=HCU4n30yi9_PE175itTbFK6T8JxzwkRAWAg@mail.gmail.com
[2] https://www.postgresql.org/message-id/7936.1526590932%40sss.pgh.pa.us

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company