Re: TRUNCATE on foreign table

2021年4月13日(火) 21:03 Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>:
>
> On Tue, Apr 13, 2021 at 2:37 PM Kohei KaiGai <kaigai(at)heterodb(dot)com> wrote:
> > Here are two points to discuss.
> >
> > Regarding to the FDW-APIs, yes, nobody can deny someone want to implement
> > their own FDW module that adds special handling when its foreign table
> > is specified
> > with ONLY-clause, even if we usually ignore.
> >
> >
> > On the other hand, when we consider a foreign table is an abstraction
> > of an external
> > data source, at least, the current postgres_fdw's behavior is not consistent.
> >
> > When a foreign table by postgres_fdw that maps a remote parent table,
> > has a local
> > child table,
> >
> > This command shows all the rows from both of local and remote.
> >
> > postgres=# select * from f_table ;
> > id | v
> > ----+-----------------------------
> > 1 | remote table t_parent id=1
> > 2 | remote table t_parent id=2
> > 3 | remote table t_parent id=3
> > 10 | remote table t_child1 id=10
> > 11 | remote table t_child1 id=11
> > 12 | remote table t_child1 id=12
> > 20 | remote table t_child2 id=20
> > 21 | remote table t_child2 id=21
> > 22 | remote table t_child2 id=22
> > 50 | it is l_child id=50
> > 51 | it is l_child id=51
> > 52 | it is l_child id=52
> > 53 | it is l_child id=53
> > (13 rows)
> >
> > If f_table is specified with "ONLY", it picks up only the parent table
> > (f_table),
> > however, ONLY-clause is not push down to the remote side.
> >
> > postgres=# select * from only f_table ;
> > id | v
> > ----+-----------------------------
> > 1 | remote table t_parent id=1
> > 2 | remote table t_parent id=2
> > 3 | remote table t_parent id=3
> > 10 | remote table t_child1 id=10
> > 11 | remote table t_child1 id=11
> > 12 | remote table t_child1 id=12
> > 20 | remote table t_child2 id=20
> > 21 | remote table t_child2 id=21
> > 22 | remote table t_child2 id=22
> > (9 rows)
> >
> > On the other hands, TRUNCATE ONLY f_table works as follows...
> >
> > postgres=# truncate only f_table;
> > TRUNCATE TABLE
> > postgres=# select * from f_table ;
> > id | v
> > ----+-----------------------------
> > 10 | remote table t_child1 id=10
> > 11 | remote table t_child1 id=11
> > 12 | remote table t_child1 id=12
> > 20 | remote table t_child2 id=20
> > 21 | remote table t_child2 id=21
> > 22 | remote table t_child2 id=22
> > 50 | it is l_child id=50
> > 51 | it is l_child id=51
> > 52 | it is l_child id=52
> > 53 | it is l_child id=53
> > (10 rows)
> >
> > It eliminates the rows only from the remote parent table although it
> > is a part of the foreign table.
> >
> > My expectation at the above command shows rows from the local child
> > table (id=50...53).
>
> 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. 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 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.
Thus, we have assumed that DML command fetches rows from the remote
parent table without ONLY-clause, once PostgreSQL picked up the foreign table
as a scan target.
I think we don't need to adjust definitions of the role of
foreign-table, even if
it represents non-RDBMS data sources.

If a foreign table by postgres_fdw supports a special table option to
indicate adding
ONLY-clause when remote query uses remote tables, it is suitable to
add ONLY-clause
on the remote TRUNCATE command also, not only SELECT/INSERT/UPDATE/DELETE.
In the other words, if a foreign-table represents only a remote parent
table, it is
suitable to truncate only the remote parent table.

Best regards,
--
HeteroDB, Inc / The PG-Strom Project
KaiGai Kohei <kaigai(at)heterodb(dot)com>