Re: TRUNCATE on foreign table

2021年4月8日(木) 15:04 Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>:
>
> On 2021/04/08 13:43, Kohei KaiGai wrote:
> > In case when a local table (with no children) has same contents,
> > TRUNCATE command
> > witll remove the entire table contents.
>
> But if there are local child tables that inherit the local parent table, and TRUNCATE ONLY <parent table> is executed, only the contents in the parent will be truncated. I was thinking that this behavior should be applied to the foreign table whose remote (parent) table have remote child tables.
>
> So what we need to reach the consensus is; how far ONLY option affects. Please imagine the case where we have
>
> (1) local parent table, also foreign table of remote parent table
> (2) local child table, inherits local parent table
> (3) remote parent table
> (4) remote child table, inherits remote parent table
>
> I think that we agree all (1), (2), (3) and (4) should be truncated if local parent table (1) is specified without ONLY in TRUNCATE command. OTOH, if ONLY is specified, we agree that at least local child table (2) should NOT be truncated.
>
My understanding of a foreign table is a representation of external
data, including remote RDBMS but not only RDBMS,
regardless of the parent-child relationship at the local side.
So, once a local foreign table wraps entire tables tree (a parent and
relevant children) at the remote side, at least, it shall
be considered as a unified data chunk from the standpoint of the local side.

Please assume if file_fdw could map 3 different CSV files, then
truncate on the foreign table may eliminate just 1 of 3 files.
Is it an expected / preferable behavior?
Basically, we don't assume any charasteristics of the data on behalf
of the FDW driver, even if it is PostgreSQL server.
Thus, I think the new API will expect to eliminate the entire rows on
behalf of the foreign table, regardless of the ONLY-clause,
because it already controls which foreign-tables shall be picked up,
but does not control which part of the foreign table
shall be eliminated.

> So the remaining point is; remote tables (3) and (4) should be truncated or not when ONLY is specified? You seem to argue that both should be truncated by removing extra list. I was thinking that only remote parent table (3) should be truncated. That is, IMO we should treat the truncation on foreign table as the same as that on its forein data source.
>
> Other people might think neither (3) nor (4) should be truncated in that case because ONLY should affect only the table directly specified in TRUNCATE command, i.e., local parent table (1). For now this also looks good to me.
>
In case when the local foreign table is a parent, the entire remote
table shall be truncated, if ONLY is given.
In case when the local foreign table is a child, nothing shall be
happen (API is not called), if ONLY is given.

IMO, it is stable and simple definition, even if FDW driver wraps
non-RDBMS data source that has no idea
of table inheritance.

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