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

Hello,

The attached is revised version.

> > If callback is invoked with a foreign-relation that is specified by TRUNCATE
> > command with ONLY, it seems to me reasonable that remote TRUNCATE
> > command specifies the relation on behalf of the foreign table with ONLY.
> >
> > So, if ExecForeignTruncate() has another list to inform the context for each
> > relation, postgres_fdw can build proper remote query that may specify the
> > remote tables with ONLY-clause.
>
> Yeah, TRUNCATE can specify ONLY on a per-table basis, so having a
> second list makes sense.  Then in the FDW, just make sure to
> elog(ERROR) if the lengths do no match, and then use forboth() to loop
> over them.  One thing that you need to be careful about is that tables
> which are added to the list because of inheritance should not be
> marked with ONLY when generating the command to the remote.
>
The v5 patch added separated list for the FDW callback, to inform the context
when relations are specified by TRUNCATE command. The frels_extra
argument is a list of integers. 0 means that relevant foreign-table is specified
without "ONLY" clause. and positive means specified with "ONLY" clause.
Negative value means that foreign-tables are not specified in the TRUNCATE
command, but truncated due to dependency (like partition's child leaf).

The remote SQL generates TRUNCATE command according to the above
"extra" information. So, "TRUNCATE ONLY ftable" generate a remote query
with "TRUNCATE ONLY mapped_remote_table".
On the other hand, it can make strange results, although it is a corner case.
The example below shows the result of TRUNCATE ONLY on a foreign-table
that mapps a remote table with an inherited children.
The rows id < 10 belongs to the parent table, thus TRUNCATE ONLY tru_ftable
eliminated the remote parent, however, it looks the tru_ftable still
contains rows
after TRUNCATE command.

I wonder whether it is tangible behavior for users. Of course, "ONLY" clause
controls local hierarchy of partitioned / inherited tables, however, I'm not
certain whether the concept shall be expanded to the structure of remote tables.

+SELECT * FROM tru_ftable;
+ id |                x
+----+----------------------------------
+  5 | e4da3b7fbbce2345d7772b0674a318d5
+  6 | 1679091c5a880faf6fb5e6087eb1b2dc
+  7 | 8f14e45fceea167a5a36dedd4bea2543
+  8 | c9f0f895fb98ab9159f51fd0297e236d
+  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
+ 10 | d3d9446802a44259755d38e6d163e820
+ 11 | 6512bd43d9caa6e02c990b0a82652dca
+ 12 | c20ad4d76fe97759aa27a0c99bff6710
+ 13 | c51ce410c124a10e0db5e4b97fc2af39
+ 14 | aab3238922bcc25a6f606eb525ffdc56
+(10 rows)
+
+TRUNCATE ONLY tru_ftable;      -- truncate only parent portion
+SELECT * FROM tru_ftable;
+ id |                x
+----+----------------------------------
+ 10 | d3d9446802a44259755d38e6d163e820
+ 11 | 6512bd43d9caa6e02c990b0a82652dca
+ 12 | c20ad4d76fe97759aa27a0c99bff6710
+ 13 | c51ce410c124a10e0db5e4b97fc2af39
+ 14 | aab3238922bcc25a6f606eb525ffdc56
+(5 rows)

> > Regarding to the other comments, it's all Ok for me. I'll update the patch.
> > And, I forgot "updatable" option at postgres_fdw. It should be checked on
> > the truncate also, right?
>
> Hmm.  Good point.  Being able to filter that silently through a
> configuration parameter is kind of interesting.  Now I think that this
> should be a separate option because updatable applies to DMLs.  Like,
> truncatable?
>
Ok, "truncatable" option was added.
Please check the regression test and documentation updates.

Best regards,
-- 
HeteroDB, Inc / The PG-Strom Project
KaiGai Kohei <kaigai@heterodb.com>