Re: [BUG?] postgres_fdw incorrectly updates remote table if it hasinherited children. - Mailing list pgsql-hackers

From Kohei KaiGai
Subject Re: [BUG?] postgres_fdw incorrectly updates remote table if it hasinherited children.
Date
Msg-id CAOP8fzajsUviPLMS9TmcejdpyFdH5wNcZSWwFG6J3H2yCh-xXg@mail.gmail.com
Whole thread Raw
In response to Re: [BUG?] postgres_fdw incorrectly updates remote table if it hasinherited children.  (Etsuro Fujita <etsuro.fujita@gmail.com>)
List pgsql-hackers
Fujita-san,

> 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.
>
I don't think the new options in postgres_fdw is a good solution because
remote table structure is flexible regardless of the local configuration in
foreign-table options. People may add inherited child tables after the
declaration of foreign-tables. It can make configuration mismatch.
Even if we always add tableoid=OID restriction on the remote query,
it shall be evaluated after the TidScan fetched the row pointed by ctid.
Its additional cost is limited.

And, one potential benefit is tableoid=OID restriction can be used to prune
unrelated partition leafs/inherited children at the planner stage.
Probably, it is a separated groundwork from postgres_fdw.
One planner considers the built-in rule for this kind of optimization,
enhancement at postgres_fdw will be quite simple, I guess.

How about your thought?

Best regards,
-- 
HeteroDB, Inc / The PG-Strom Project
KaiGai Kohei <kaigai@heterodb.com>



pgsql-hackers by date:

Previous
From: Julien Rouhaud
Date:
Subject: Re: Planning counters in pg_stat_statements (using pgss_store)
Next
From: Daniel Gustafsson
Date:
Subject: Re: Change atoi to strtol in same place