Re: TRUNCATE on foreign table - Mailing list pgsql-hackers

At Fri, 16 Apr 2021 11:54:16 +0900, Fujii Masao <masao.fujii@oss.nttdata.com> wrote in 
> On 2021/04/16 9:15, Bharath Rupireddy wrote:
> > On Thu, Apr 15, 2021 at 8:19 PM Fujii Masao <masao.fujii@oss.nttdata.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
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 1a3f5cb4ad..d32f291089 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8388,7 +8388,7 @@ SELECT sum(id) FROM tru_ftable;   -- 95
   95
 (1 row)
 
-TRUNCATE ONLY tru_ftable;        -- truncate both parent and child
+TRUNCATE ONLY tru_ftable;        -- truncate both of parent and child
 SELECT count(*) FROM tru_ftable;   -- 0
  count 
 -------
@@ -8396,10 +8396,11 @@ SELECT count(*) FROM tru_ftable;   -- 0
 (1 row)
 
 INSERT INTO tru_rtable0 (SELECT x FROM generate_series(21,25) x);
-SELECT sum(id) FROM tru_ftable;        -- 115
+INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(26,30) x);
+SELECT sum(id) FROM tru_ftable;        -- 255
  sum 
 -----
- 115
+ 255
 (1 row)
 
 TRUNCATE tru_ftable;            -- truncate both of parent and child
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 97c156a472..65643e120d 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2439,11 +2439,12 @@ INSERT INTO tru_rtable0 (SELECT x FROM generate_series(5,9) x);
 INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(10,14) x);
 SELECT sum(id) FROM tru_ftable;   -- 95
 
-TRUNCATE ONLY tru_ftable;        -- truncate both parent and child
+TRUNCATE ONLY tru_ftable;        -- truncate both of parent and child
 SELECT count(*) FROM tru_ftable;   -- 0
 
 INSERT INTO tru_rtable0 (SELECT x FROM generate_series(21,25) x);
-SELECT sum(id) FROM tru_ftable;        -- 115
+INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(26,30) x);
+SELECT sum(id) FROM tru_ftable;        -- 255
 TRUNCATE tru_ftable;            -- truncate both of parent and child
 SELECT count(*) FROM tru_ftable;    -- 0