Re: TRUNCATE on foreign table

On Tue, Apr 13, 2021 at 8:30 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
> On 2021/04/13 23:25, Kohei KaiGai wrote:
> > 2021年4月13日(火) 21:03 Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)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?

IMHO, we can push all the TRUNCATE options (ONLY, RESTRICTED, CASCADE,
RESTART/CONTINUE IDENTITY), because it doesn't have any major
challenge(implementation wise) unlike pushing some clauses in
SELECT/UPDATE/DELETE and we already do this on the master. It doesn't
look good and may confuse users, if we push some options and restrict
others. We should have an explicit note in the documentation saying we
push all these options to the remote server. We can leave it to the
user to write TRUNCATE for foreign tables with the appropriate
options. If somebody complains about a problem that they will face
with this behavior, we can revisit. This is my opinion, others may
disagree.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com