Re: TRUNCATE on foreign table

On 2021/04/14 13:41, Kyotaro Horiguchi wrote:
> At Wed, 14 Apr 2021 13:17:55 +0900, Kohei KaiGai <kaigai@heterodb.com> wrote in
>> Please assume the internal heap data is managed by PostgreSQL core, and
>> external data source is managed by postgres_fdw (or other FDW driver).
>> TRUNCATE command requires these object managers to eliminate the data
>> on behalf of the foreign tables picked up.
>>
>> Even though the object manager tries to eliminate the managed data, it may be
>> restricted by some reason; FK restrictions in case of PostgreSQL internal data.
>> In this case, CASCADE/RESTRICT option suggests the object manager how
>> to handle the target data.
>>
>> The ONLY clause controls whoes data shall be eliminated.
>> On the other hand, CASCADE/RESTRICT and CONTINUE/RESTART controls
>> how data shall be eliminated. It is a primitive difference.

I have a different view on this classification. IMO ONLY and RESTRICT/CASCADE
should be categorized into the same group. Because both options specify
whether to truncate dependent tables or not. If we treat a foreign table as
an abstraction of external data source, ISTM that we should not take care of
table dependancy in the remote server. IOW, we should truncate entire
external data source, i.e., postgres_fdw should push neither ONLY nor
RESTRICT down to the remote server.


> I object to unconditionally push ONLY to remote. As Kaigai-san said
> that it works an apparent wrong way when a user wants to truncate only
> the specified foreign table in a inheritance tree and there's no way to
> avoid the behavior.
> 
> I also don't think it is right to push down CASCADE/RESTRICT.  The
> options suggest to propagate truncation to *local* referrer tables
> from the *foreign* table, not to the remote referrer tables from the
> original table on remote.

Agreed.


> If a user want to allow that behavior it
> should be specified by foreign table options.  (It is bothersome when
> someone wants to specify the behavior on-the-fly.)
> 
> alter foreign table ft1 options (add truncate_cascade 'true');

Maybe. I think this is the item for v15 or later.


> Also, CONTINUE/RESTART IDENTITY should not work since foreign tables
> don't have an identity-sequence. However, this we might be able to
> push down the options since it affects only the target table.

+1

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION