Thread

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

Daniil Davydov <3danissimo@gmail.com> — 2025-10-01T15:18:06Z

Hi,

On Fri, Nov 16, 2018 at 6:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:
> > [ fix-foreign-modify-efujita-2.patch ]
>
> Um ... wow, I do not like anything about this.  Adding a "tableoid = X"
> constraint to every remote update query seems awfully expensive,
> considering that (a) it's useless for non-partitioned tables, and
> (b) the remote planner will have exactly no intelligence about handling
> it.  We could improve (b) probably, but that'd be another big chunk of
> work, and it wouldn't help when talking to older servers.
>
> (Admittedly, I'm not sure I have a better idea.  If we knew which
> remote tables were partitioned, we could avoid sending unnecessary
> tableoid constraints; but we don't have any good way to track that.)
>

About point (a) :
Actually, we can request tableoid to be included into the result row (for
the ForeignScan node) and then  check whether tableoid matches the oid
of the foreign table. If not - we will know that  tuple is stored in a
partition.

Thus, we will request tableoid only when we will know that we are
scanning foreign partitioned table.

>
> I wonder whether we'd be better off thinking of a way to let FDWs
> invent additional system column IDs for their tables, so that
> something like a remote table OID could be represented in the
> natural way as a Var with negative varattno.
>

May I ask you to tell me more details? As far as I understand, if
postgres_fdw doesn't use direct modify, it will hardcode query like
this anyway :
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
appendStringInfoString(buf, " WHERE ctid = $1");

At the moment when we set the ctid in this way, the situation inevitably
moves towards an error.

Are you suggesting using another condition for the WHERE clause in
this case (based on the new "remote table oid" column)?

--
Best regards,
Daniil Davydov