Re: TRUNCATE on foreign table

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.

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

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center