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

On Tue, Apr 13, 2021 at 2:37 PM Kohei KaiGai <kaigai@heterodb.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.

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