2021年4月13日(火) 21:03 Bharath Rupireddy <bharath.rupireddyforpostg...@gmail.com>: > > On Tue, Apr 13, 2021 at 2:37 PM Kohei KaiGai <kai...@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. > 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 <kai...@heterodb.com>
