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

On Thu, May 31, 2018 at 11:34 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> On Thu, May 31, 2018 at 7:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What you want for the first part of that is basically like
>> generate_new_param() in subselect.c. We don't expose that publicly
>> at the moment, but we could, or maybe better to invent another wrapper
>> around it like SS_make_initplan_output_param.
>
> This looks like a lot of change which might take some time and may not

I agree. It needs at least, in a short sight, an additional parameter
(PlannerInfo in a straightforwad way) for
postgresAddForeignUpdateTargets which is a change of FDW-API.

> be back-portable. In the mean time, can we see if 0001 and 0002
> patches are good and apply them. Those patches intend to stop the
> multiple rows on the foreign server being updated by throwing error
> (and aborting the transaction on the foreign server) when that
> happens. That will at least avoid silent corruption that happens today
> and should be back-portable.
>
> [1] https://www.postgresql.org/message-id/CAFjFpRfK69ptCTNChBBk+LYMXFzJ92SW6NmG4HLn_1y7xFk=kw@mail.gmail.com

Having said that I think that storing oids of the remote table in
local tableoid syscolumn is a breakage of the existing contract about
the field. (I wish this is comprehensible.)
However I haven't found a way to "fix" this without such breakage of
API thus it seems to me inevitable to leave this problem as a
restriction, we still can avoid the problematic behavior by explicitly
declaring remote tableoid column (like the "key" column option of
oracle-fdw).

CREATE FOREIGN TABLE ft1 (rtoid oid, a int, blah, blah) SERVER sv
OPTIONS (remote_tableoid 'rtoid', table_name 'lt1');

However, of-course the proposed fix will work if we allow the
a-kind-of illegal usage of the local tableoid. And it seems to be a
way to cause a series of frequent changes on the same feature.

Thoughts?