Re: [BUG?] postgres_fdw incorrectly updates remote table if it has inherited children.

Hi KaiGai-san,

On Sun, Mar 1, 2020 at 1:47 PM Kohei KaiGai <kaigai(at)heterodb(dot)com> wrote:
> It looks to me the latest patch was submitted by Fujita-san, Oct-2018.
> Then, Tom pointer out this simple approach has a problem of inefficient remote
> query plan because of no intelligence on the structure of remote tables mapped
> by postgres_fdw. After that, the patch has been left for a year.

Unfortunately, I didn't have time to work on that (and won't in the
development cycle for PG13.)

> Indeed, it is not an ideal query plan to execute for each updated rows...
>
> postgres=# explain select * from rtable_parent where tableoid = 126397
> and ctid = '(0,11)'::tid;
> QUERY PLAN
> -------------------------------------------------------------------------
> Append (cost=0.00..5.18 rows=2 width=50)
> -> Seq Scan on rtable_parent (cost=0.00..1.15 rows=1 width=31)
> Filter: ((tableoid = '126397'::oid) AND (ctid = '(0,11)'::tid))
> -> Tid Scan on rtable_child (cost=0.00..4.02 rows=1 width=68)
> TID Cond: (ctid = '(0,11)'::tid)
> Filter: (tableoid = '126397'::oid)
> (6 rows)

IIRC, I think one of Tom's concerns about the solution I proposed was
that it added the tableoid restriction clause to the remote
UPDATE/DELETE query even if the remote table is not an inheritance
set. To add the clause only if the remote table is an inheritance
set, what I have in mind is to 1) introduce a new postgres_fdw table
option to indicate whether the remote table is an inheritance set or
not, and 2) determine whether to add the clause or not, using the
option.

Best regards,
Etsuro Fujita