Re: TRUNCATE on foreign table

On 2021/04/16 14:20, Kyotaro Horiguchi wrote:
> At Fri, 16 Apr 2021 11:54:16 +0900, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote in
>> On 2021/04/16 9:15, Bharath Rupireddy wrote:
>>> On Thu, Apr 15, 2021 at 8:19 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
>>> wrote:
>>>> On 2021/04/14 12:54, Bharath Rupireddy wrote:
>>>>> IMHO, we can push all the TRUNCATE options (ONLY, RESTRICTED, CASCADE,
>>>>> RESTART/CONTINUE IDENTITY), because it doesn't have any major
>>>>> challenge(implementation wise) unlike pushing some clauses in
>>>>> SELECT/UPDATE/DELETE and we already do this on the master. It doesn't
>>>>> look good and may confuse users, if we push some options and restrict
>>>>> others. We should have an explicit note in the documentation saying we
>>>>> push all these options to the remote server. We can leave it to the
>>>>> user to write TRUNCATE for foreign tables with the appropriate
>>>>> options. If somebody complains about a problem that they will face
>>>>> with this behavior, we can revisit.
>>>>
>>>> That's one of the options. But I'm afraid it's hard to drop (revisit)
>>>> the feature once it has been released. So if there is no explicit
>>>> use case for that, basically I'd like to drop that before release
>>>> like we agree to drop unused TRUNCATE_REL_CONTEXT_CASCADING.
>>> Thanks. Looks like the decision is going in the direction of
>>> restricting those options, I will withdraw my point.
>>
>> We are still discussing whether RESTRICT option should be pushed down to
>> a foreign data wrapper. But ISTM at least we could reach the consensus about
>> the drop of extra information for each foreign table. So what about applying
>> the attached patch and remove the extra information at first?
>
> I'm fine with that direction. Thanks for the patch.
>
> The change is straight-forward and looks fine, except the following
> part.
>
> ==== contrib/postgres_fdw/sql/postgres_fdw.sql: 2436 -- after patching
> 2436> -- in case when remote table has inherited children
> 2437> CREATE TABLE tru_rtable0_child () INHERITS (tru_rtable0);
> 2438> INSERT INTO tru_rtable0 (SELECT x FROM generate_series(5,9) x);
> 2439> INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(10,14) x);
> 2440> SELECT sum(id) FROM tru_ftable; -- 95
> 2441>
> 2442> TRUNCATE ONLY tru_ftable; -- truncate both parent and child
> 2443> SELECT count(*) FROM tru_ftable; -- 0
> 2444>
> 2445> INSERT INTO tru_rtable0 (SELECT x FROM generate_series(21,25) x);
> 2446> SELECT sum(id) FROM tru_ftable; -- 115
> 2447> TRUNCATE tru_ftable; -- truncate both of parent and child
> 2448> SELECT count(*) FROM tru_ftable; -- 0
>
> L2445-L2448 doesn't work as described since L2445 inserts tuples only
> to the parent.
>
> And there's a slight difference for no reason between the comment at
> 2442 and 2447.

Agreed. Thanks!

> (The attached is a fix on top of the proposed patch.)

I will include this patch into the main patch.

Regards,

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