Re: TRUNCATE on foreign table - Mailing list pgsql-hackers

At Wed, 14 Apr 2021 13:17:55 +0900, Kohei KaiGai <kaigai@heterodb.com> wrote in 
> 2021年4月14日(水) 0:00 Fujii Masao <masao.fujii@oss.nttdata.com>:
> >
> > On 2021/04/13 23:25, Kohei KaiGai wrote:
> > > 2021年4月13日(火) 21:03 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>:
> > >> Yeah, ONLY clause is not pushed to the remote server in case of SELECT
> > >> commands. This is also true for DELETE and UPDATE commands on foreign
> > >> tables.
> >
> > This sounds reasonable reason why ONLY should be ignored in TRUNCATE on
> > foreign tables, for now. If there is the existing rule about how to treat
> > ONLY clause for foreign tables, basically TRUNCATE should follow that at this
> > stage. Maybe we can change the rule, but it's an item for v15 or later?
> >
> >
> > >> I'm not sure if it wasn't thought necessary or if there is an
> > >> issue to push it or I may be missing something here.
> >
> > I could not find the past discussion about foreign tables and ONLY clause.
> > I guess that ONLY is ignored in SELECT on foreign tables case because ONLY
> > is interpreted outside the executor and it's not easy to change the executor
> > so that ONLY is passed to FDW. Maybe..
> >
> >
> > >> I think we can
> > >> start a separate thread to see other hackers' opinions on this.
> > >>
> > >> I'm not sure whether all the clauses that are possible for
> > >> SELECT/UPDATE/DELETE/INSERT with local tables are pushed to the remote
> > >> server by postgres_fdw.
> > >>
> > >> Well, now foreign TRUNCATE pushes the ONLY clause to the remote server
> > >> which is inconsistent when compared to SELECT/UPDATE/DELETE commands.
> > >> If we were to keep it consistent across all foreign commands that
> > >> ONLY clause is not pushed to remote server, then we can restrict for
> > >> TRUNCATE too and even if "TRUNCATE ONLY foreign_tbl" is specified,
> > >> just pass "TRUNCATE foreign_tbl" to remote server. Having said that, I
> > >> don't see any real problem in pushing the ONLY clause, at least in
> > >> case of TRUNCATE.
> > >>
> > > If ONLY-clause would be pushed down to the remote query of postgres_fdw,
> > > what does the foreign-table represent in the local system?
> > >
> > > In my understanding, a local foreign table by postgres_fdw is a
> > > representation of
> > > entire tree of the remote parent table and its children.
> >
> > If so, I'm still wondering why CASCADE/RESTRICT (i.e., DropBehavior) needs to
> > be passed to FDW. IOW, if a foreign table is an abstraction of an external
> > data source, ISTM that postgres_fdw should always issue TRUNCATE with
> > CASCADE. Why do we need to allow RESTRICT to be specified for a foreign table
> > even though it's an abstraction of an external data source?
> >
> Please assume the internal heap data is managed by PostgreSQL core, and
> external data source is managed by postgres_fdw (or other FDW driver).
> TRUNCATE command requires these object managers to eliminate the data
> on behalf of the foreign tables picked up.
> 
> Even though the object manager tries to eliminate the managed data, it may be
> restricted by some reason; FK restrictions in case of PostgreSQL internal data.
> In this case, CASCADE/RESTRICT option suggests the object manager how
> to handle the target data.
> 
> The ONLY clause controls whoes data shall be eliminated.
> On the other hand, CASCADE/RESTRICT and CONTINUE/RESTART controls
> how data shall be eliminated. It is a primitive difference.

I object to unconditionally push ONLY to remote. As Kaigai-san said
that it works an apparent wrong way when a user wants to truncate only
the specified foreign table in a inheritance tree and there's no way to
avoid the behavior.

I also don't think it is right to push down CASCADE/RESTRICT.  The
options suggest to propagate truncation to *local* referrer tables
from the *foreign* table, not to the remote referrer tables from the
original table on remote. If a user want to allow that behavior it
should be specified by foreign table options.  (It is bothersome when
someone wants to specify the behavior on-the-fly.)

alter foreign table ft1 options (add truncate_cascade 'true');

Also, CONTINUE/RESTART IDENTITY should not work since foreign tables
don't have an identity-sequence. However, this we might be able to
push down the options since it affects only the target table.

I would accept that behavior if TRUNCATE were "TRUNCATE FOREIGN
TABLE", which explicitly targets a foreign table. But I'm not sure it
is possible to add such syntax reasonable way.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center