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

On Thu, May 31, 2018 at 7:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kyotaro HORIGUCHI <kyota(dot)horiguchi(at)gmail(dot)com> writes:
>> If my understanding about non-system junk columns in a base relation
>> and identifiers of a foreign tuples are correct, what is needed here
>> is giving base relations the ability to have such junk column.
>
> The core of the problem, I think, is the question of exactly what
> postgresAddForeignUpdateTargets should put into the resjunk expressions
> it adds to an update/delete query's targetlist. Per discussion yesterday,
> up to now it's always emitted Vars referencing the foreign relation,
> which is problematic because with that approach the desired info has
> to be exposed as either a regular or system column of that relation.
> But there's nothing saying that the expression has to be a Var.
>
> My thought about what we might do instead is that
> postgresAddForeignUpdateTargets could reserve a PARAM_EXEC slot
> and emit a Param node referencing that. Then at runtime, while
> reading a potential target row from the remote, we fill that
> param slot along with the regular scan tuple slot.
>
> 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
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

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