Re: TRUNCATE on foreign table

2021年4月13日(火) 16:17 Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>:
>
> On 2021/04/13 14:22, Kohei KaiGai wrote:
> > Let me remind the discussion at the design level.
> >
> > If postgres_fdw (and other FDW drivers) needs to consider whether
> > ONLY-clause is given
> > on the foreign tables of them, what does a foreign table represent in
> > PostgreSQL system?
> >
> > My assumption is, a foreign table provides a view to external data, as
> > if it performs like a table.
> > TRUNCATE command eliminates all the segment files, even if a table
> > contains multiple
> > underlying files, never eliminate them partially.
> > If a foreign table is equivalent to a table in SQL operation level,
> > indeed, ONLY-clause controls
> > which tables are picked up by the TRUNCATE command, but never controls
> > which portion of
> > the data shall be eliminated. So, I conclude that
> > ExecForeignTruncate() shall eliminate the entire
> > external data on behalf of a foreign table, regardless of ONLY-clause.
> >
> > I think it is more significant to clarify prior to the implementation details.
> > How about your opinions?
>
> I'm still thinking that it's better to pass all information including
> ONLY clause about TRUNCATE command to FDW and leave FDW to determine
> how to use them. How postgres_fdw should use the information about ONLY
> is debetable. But for now IMO that users who explicitly specify ONLY clause for
> foreign tables understand the structure of remote tables and want to use ONLY
> in TRUNCATE command issued by postgres_fdw. But my opinion might be minority,
> so I'd like to hear more opinion about this, from other developers.
>
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).

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