TRUNCATE on foreign table

Hi,
+               if (strcmp(defel->defname, "truncatable") == 0)
+                   server_truncatable = defGetBoolean(defel);

Looks like we can break out of the loop when the condition is met.

+ /* ExecForeignTruncate() is invoked for each server */

The method name in the comment is slightly different from the actual method
name.

+           if (strcmp(defel->defname, "truncatable") == 0)
+               truncatable = defGetBoolean(defel);

We can break out of the loop when the condition is met.

Cheers

On Sat, Feb 6, 2021 at 5:11 AM Kazutaka Onishi <onishi@heterodb.com> wrote:

Thank you for your comment! :D
I have fixed it and attached the revised patch.

regards,

2021年2月7日(日) 2:08 Zhihong Yu <zyu@yugabyte.com>:

Hi,
+               if (strcmp(defel->defname, "truncatable") == 0)
+                   server_truncatable = defGetBoolean(defel);

Looks like we can break out of the loop when the condition is met.

+ /* ExecForeignTruncate() is invoked for each server */

The method name in the comment is slightly different from the actual
method name.

+           if (strcmp(defel->defname, "truncatable") == 0)
+               truncatable = defGetBoolean(defel);

We can break out of the loop when the condition is met.

Cheers

On Sat, Feb 6, 2021 at 5:11 AM Kazutaka Onishi <onishi@heterodb.com>
wrote:

Hello,

The attached patch is for supporting "TRUNCATE" on foreign tables.

This patch includes:
* Adding "ExecForeignTruncate" function into FdwRoutine.
* Enabling "postgres_fdw" to use TRUNCATE.

This patch was proposed by Kaigai-san in March 2020,
but it was returned because it can't be applied to the latest source
codes.

Please refer to the discussion.

/messages/by-id/CAOP8fzb-t3WVNLjGMC+4sV4AZa9S=MAQ7Q6pQoADMCf_1jp4ew@mail.gmail.com

I have fixed the patch due to submit it to Commit Fest 2021-03.

regards,

--
------------------
Kazutaka Onishi
(onishi@heterodb.com)

--
------------------
Kazutaka Onishi
(onishi@heterodb.com)

--
------------------
Kazutaka Onishi
(onishi@heterodb.com)

IIUC, "truncatable" would be set to "false" for relations which do not
have physical storage e.g. views but will be true for regular tables.
When we are importing schema we need to set "truncatable"
appropriately. Is that something we will support with this patch?

Why would one want to truncate a foreign table instead of truncating
actual table wherever it is?

On Sun, Feb 7, 2021 at 6:06 PM Kazutaka Onishi <onishi@heterodb.com> wrote:

Thank you for your comment! :D
I have fixed it and attached the revised patch.

regards,

2021年2月7日(日) 2:08 Zhihong Yu <zyu@yugabyte.com>:

Hi,
+               if (strcmp(defel->defname, "truncatable") == 0)
+                   server_truncatable = defGetBoolean(defel);

Looks like we can break out of the loop when the condition is met.

+ /* ExecForeignTruncate() is invoked for each server */

The method name in the comment is slightly different from the actual method name.

+           if (strcmp(defel->defname, "truncatable") == 0)
+               truncatable = defGetBoolean(defel);

We can break out of the loop when the condition is met.

Cheers

On Sat, Feb 6, 2021 at 5:11 AM Kazutaka Onishi <onishi@heterodb.com> wrote:

Hello,

The attached patch is for supporting "TRUNCATE" on foreign tables.

This patch includes:
* Adding "ExecForeignTruncate" function into FdwRoutine.
* Enabling "postgres_fdw" to use TRUNCATE.

This patch was proposed by Kaigai-san in March 2020,
but it was returned because it can't be applied to the latest source codes.

Please refer to the discussion.
/messages/by-id/CAOP8fzb-t3WVNLjGMC+4sV4AZa9S=MAQ7Q6pQoADMCf_1jp4ew@mail.gmail.com

I have fixed the patch due to submit it to Commit Fest 2021-03.

regards,

--
------------------
Kazutaka Onishi
(onishi@heterodb.com)

--
------------------
Kazutaka Onishi
(onishi@heterodb.com)

--
------------------
Kazutaka Onishi
(onishi@heterodb.com)

--
Best Wishes,
Ashutosh Bapat

On Tue, Feb 9, 2021 at 5:31 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Why would one want to truncate a foreign table instead of truncating
actual table wherever it is?

I think when the deletion on foreign tables (which actually deletes
rows from the remote table?) is allowed, it does make sense to have a
way to truncate the remote table via foreign table. Also, it can avoid
going to each and every remote server and doing the truncation
instead.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com

On Tue, Feb 9, 2021 at 5:49 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

On Tue, Feb 9, 2021 at 5:31 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Why would one want to truncate a foreign table instead of truncating
actual table wherever it is?

I think when the deletion on foreign tables (which actually deletes
rows from the remote table?) is allowed, it does make sense to have a
way to truncate the remote table via foreign table. Also, it can avoid
going to each and every remote server and doing the truncation
instead.

DELETE is very different from TRUNCATE. Application may want to DELETE
based on a join with a local table and hence it can not be executed on
a foreign server. That's not true with TRUNCATE.

--
Best Wishes,
Ashutosh Bapat

IIUC, "truncatable" would be set to "false" for relations which do not
have physical storage e.g. views but will be true for regular tables.

"truncatable" option is just for the foreign table and it's not related
with whether it's on a physical storage or not.
"postgres_fdw" already has "updatable" option to make the table read-only.
However, "updatable" is for DML, and it's not suitable for TRUNCATE.
Therefore new options "truncatable" was added.

Please refer to this message for details.
/messages/by-id/20200128040346.GC1552@paquier.xyz

DELETE is very different from TRUNCATE. Application may want to DELETE
based on a join with a local table and hence it can not be executed on
a foreign server. That's not true with TRUNCATE.

Yeah, As you say, Applications doesn't need TRUNCATE.
We're focusing for analytical use, namely operating huge data.
TRUNCATE can erase rows faster than DELETE.

On Tue, Feb 9, 2021 at 7:45 PM Kazutaka Onishi <onishi@heterodb.com> wrote:

IIUC, "truncatable" would be set to "false" for relations which do not
have physical storage e.g. views but will be true for regular tables.

"truncatable" option is just for the foreign table and it's not related with whether it's on a physical storage or not.
"postgres_fdw" already has "updatable" option to make the table read-only.
However, "updatable" is for DML, and it's not suitable for TRUNCATE.
Therefore new options "truncatable" was added.

Please refer to this message for details.
/messages/by-id/20200128040346.GC1552@paquier.xyz

DELETE is very different from TRUNCATE. Application may want to DELETE
based on a join with a local table and hence it can not be executed on
a foreign server. That's not true with TRUNCATE.

Yeah, As you say, Applications doesn't need TRUNCATE.
We're focusing for analytical use, namely operating huge data.
TRUNCATE can erase rows faster than DELETE.

The question is why can't that truncate be run on the foreign server
itself rather than local server?

--
Best Wishes,
Ashutosh Bapat

That's because using the foreign server is difficult for the user.

For example, the user doesn't always have the permission to login to
the forein server.
In some cases, the foreign table has been created by the administrator that
has permission to access the two servers and the user only uses the local
server.
Then the user has to ask the administrator to run TRUNCATE every time.

Furthermore,there are some fdw extensions which don't support SQL.
mongo_fdw, redis_fdw, etc...
These extensions have been used to provide SQL interfaces to the users.
It's hard for the user to run TRUNCATE after learning each database.

Anyway, it's more useful if the user can run queries in one place, right?
Do you have any concerns?

2021年2月10日(水) 13:55 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>:

On Tue, Feb 9, 2021 at 7:45 PM Kazutaka Onishi <onishi@heterodb.com> wrote:

IIUC, "truncatable" would be set to "false" for relations which do not
have physical storage e.g. views but will be true for regular tables.

"truncatable" option is just for the foreign table and it's not related with whether it's on a physical storage or not.
"postgres_fdw" already has "updatable" option to make the table read-only.
However, "updatable" is for DML, and it's not suitable for TRUNCATE.
Therefore new options "truncatable" was added.

Please refer to this message for details.
/messages/by-id/20200128040346.GC1552@paquier.xyz

DELETE is very different from TRUNCATE. Application may want to DELETE
based on a join with a local table and hence it can not be executed on
a foreign server. That's not true with TRUNCATE.

Yeah, As you say, Applications doesn't need TRUNCATE.
We're focusing for analytical use, namely operating huge data.
TRUNCATE can erase rows faster than DELETE.

The question is why can't that truncate be run on the foreign server
itself rather than local server?

At least, PostgreSQL applies different access permissions on TRUNCATE.
If unconditional DELETE implicitly promotes to TRUNCATE, DB administrator
has to allow TRUNCATE permission on the remote table also.

Also, TRUNCATE acquires stronger lock the DELETE.
DELETE still allows concurrent accesses to the table, even though TRUNCATE
takes AccessExclusive lock, thus, FDW driver has to control the
concurrent accesses
by itself, if we have no dedicated TRUNCATE interface.

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

On Wed, Feb 10, 2021 at 10:58 PM Kazutaka Onishi <onishi@heterodb.com> wrote:

That's because using the foreign server is difficult for the user.

For example, the user doesn't always have the permission to login to the forein server.
In some cases, the foreign table has been created by the administrator that has permission to access the two servers and the user only uses the local server.
Then the user has to ask the administrator to run TRUNCATE every time.

That might actually be seen as a loophole but ...

Furthermore,there are some fdw extensions which don't support SQL. mongo_fdw, redis_fdw, etc...
These extensions have been used to provide SQL interfaces to the users.
It's hard for the user to run TRUNCATE after learning each database.

this has some appeal.

Thanks for sharing the usecases.
--
Best Wishes,
Ashutosh Bapat

On Thu, Feb 11, 2021 at 6:23 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
wrote:

On Wed, Feb 10, 2021 at 10:58 PM Kazutaka Onishi <onishi@heterodb.com>
wrote:

That's because using the foreign server is difficult for the user.

For example, the user doesn't always have the permission to login to the

forein server.

In some cases, the foreign table has been created by the administrator

that has permission to access the two servers and the user only uses the
local server.

Then the user has to ask the administrator to run TRUNCATE every time.

That might actually be seen as a loophole but ...

Furthermore,there are some fdw extensions which don't support SQL.

mongo_fdw, redis_fdw, etc...

These extensions have been used to provide SQL interfaces to the users.
It's hard for the user to run TRUNCATE after learning each database.

this has some appeal.

Thanks for sharing the usecases.
--
Best Wishes,
Ashutosh Bapat

The patch (pgsql14-truncate-on-foreign-table.v2.patch) does not apply

successfully.

http://cfbot.cputube.org/patch_32_2972.log

patching file contrib/postgres_fdw/expected/postgres_fdw.out
Hunk #2 FAILED at 9179.
1 out of 2 hunks FAILED -- saving rejects to file
contrib/postgres_fdw/expected/postgres_fdw.out.rej

As this is a minor change therefore I have updated the patch. Please take a
look.

--
Ibrar Ahmed

On Tue, Mar 9, 2021 at 2:24 AM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:

The patch (pgsql14-truncate-on-foreign-table.v2.patch) does not apply successfully.

http://cfbot.cputube.org/patch_32_2972.log

patching file contrib/postgres_fdw/expected/postgres_fdw.out
Hunk #2 FAILED at 9179.
1 out of 2 hunks FAILED -- saving rejects to file contrib/postgres_fdw/expected/postgres_fdw.out.rej

As this is a minor change therefore I have updated the patch. Please take a look.

Thanks for updating the patch. I was able to apply it successfully
though I notice it doesn't pass make check-world.

Specifically, it fails the src/test/subscription/013_partition.pl
test. The problem seems to be that worker.c: apply_handle_truncate()
hasn't been updated to add entries to relids_extra for partitions
expanded from a partitioned table, like ExecuteTruncate() does. That
leads to relids and relids_extra having different lengths, which trips
the Assert in ExecuteTruncateGuts().

--
Amit Langote
EDB: http://www.enterprisedb.com

To Ibrar,
Thank you for updating the patch!

To Amit,
Thank you for checking the patch, and I have confirmed the failure.
Now I'm trying to fix it.

2021年3月9日(火) 11:54 Amit Langote <amitlangote09@gmail.com>:

I have fixed the patch to pass check-world test. :D

2021年3月13日(土) 12:35 Kazutaka Onishi <onishi@heterodb.com>:

On 2021/03/13 18:57, Kazutaka Onishi wrote:

I have fixed the patch to pass check-world test. :D

Thanks for updating the patch! Here are some review comments from me.

By default all foreign tables using <filename>postgres_fdw</filename> are assumed
to be updatable. This may be overridden using the following option:

In postgres-fdw.sgml, "and truncatable" should be appended into
the above first description? Also "option" in the second description
should be a plural form "options"?

<command>TRUNCATE</command> is not currently supported for foreign tables.
This implies that if a specified table has any descendant tables that are
foreign, the command will fail.

truncate.sgml should be updated because, for example, it contains
the above descriptions.

+     <literal>frels_extra</literal> is same length with
+     <literal>frels_list</literal>, that delivers extra information of
+     the context where the foreign-tables are truncated.
+    </para>

Don't we need to document the detail information about frels_extra?
Otherwise the developers of FDW would fail to understand how to
handle the frels_extra when trying to make their FDWs support TRUNCATE.

+		relids_extra = lappend_int(relids_extra, (recurse ? 0 : 1));
+				relids_extra = lappend_int(relids_extra, -1);

postgres_fdw determines whether to specify ONLY or not by checking
whether the passed extra value is zero or not. That is, for example,
using only 0 and 1 for extra values is enough for the purpose. But
ExecuteTruncate() sets three values 0, -1 and 1 as extra ones. Why are
these three values necessary?

With the patch, if both local and foreign tables are specified as
the target tables to truncate, TRUNCATE command tries to truncate
foreign tables after truncating local ones. That is, if "truncatable"
option is set to false or enough permission to truncate is not granted
yet in the foreign server, an error will be thrown after the local tables
are truncated. I don't think this is good order of processings. IMO,
instead, we should check whether foreign tables can be truncated
before any actual truncation operations. For example, we can easily
do that by truncate foreign tables before local ones. Thought?

XLOG_HEAP_TRUNCATE record is written even for the truncation of
a foreign table. Why is this necessary?

Regards,

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

Fujii-san,

Thank you for your review!
Now I prepare v5 patch and I'll answer to your each comment. please
check this again.
m(_ _)m

1. In postgres-fdw.sgml, "and truncatable" should be appended into the
above first description?
2. truncate.sgml should be updated because, for example, it contains
the above descriptions.

Yeah, you're right. I've fixed it.

3. Don't we need to document the detail information about frels_extra?

I've written about frels_extra into fdwhander.sgml.

4. postgres_fdw determines whether to specify ONLY or not by checking
whether the passed extra value is zero or not.

Please refer this:
/messages/by-id/CAOP8fzb-t3WVNLjGMC+4sV4AZa9S=MAQ7Q6pQoADMCf_1jp4ew@mail.gmail.com

Negative value means that foreign-tables are not specified in the TRUNCATE
command, but truncated due to dependency (like partition's child leaf).

I've added this information into fdwhandler.sgml.

5. For example, we can easily do that by truncate foreign tables
before local ones. Thought?

Umm... yeah, I feel it's better procedure, but not so required because
TRUNCATE is NOT called frequently.
Certainly, we already have postgresIsForeignUpdatable() to check
whether the foreign table is updatable or not.
Following this way, we have to add postgresIsForeignTruncatable() to check.
However, Unlike UPDATE, TRUNCATE is NOT called frequently. Current
procedure is inefficient but works correctly.
Thus, I feel postgresIsForeignTruncatable() is not needed.

6. XLOG_HEAP_TRUNCATE record is written even for the truncation of a
foreign table. Why is this necessary?

Please give us more time to investigate this.

2021年3月25日(木) 3:47 Fujii Masao <masao.fujii@oss.nttdata.com>:

Onishi-san,

The v5 patch contains full-contents of "src/backend/commands/tablecmds.c.orig".
Please check it.

2021年3月28日(日) 2:37 Kazutaka Onishi <onishi@heterodb.com>:

Fujii-san,

Thank you for your review!
Now I prepare v5 patch and I'll answer to your each comment. please
check this again.
m(_ _)m

1. In postgres-fdw.sgml, "and truncatable" should be appended into the
above first description?
2. truncate.sgml should be updated because, for example, it contains
the above descriptions.

Yeah, you're right. I've fixed it.

3. Don't we need to document the detail information about frels_extra?

I've written about frels_extra into fdwhander.sgml.

4. postgres_fdw determines whether to specify ONLY or not by checking
whether the passed extra value is zero or not.

Please refer this:
/messages/by-id/CAOP8fzb-t3WVNLjGMC+4sV4AZa9S=MAQ7Q6pQoADMCf_1jp4ew@mail.gmail.com

Negative value means that foreign-tables are not specified in the TRUNCATE
command, but truncated due to dependency (like partition's child leaf).

I've added this information into fdwhandler.sgml.

5. For example, we can easily do that by truncate foreign tables
before local ones. Thought?

Umm... yeah, I feel it's better procedure, but not so required because
TRUNCATE is NOT called frequently.
Certainly, we already have postgresIsForeignUpdatable() to check
whether the foreign table is updatable or not.
Following this way, we have to add postgresIsForeignTruncatable() to check.
However, Unlike UPDATE, TRUNCATE is NOT called frequently. Current
procedure is inefficient but works correctly.
Thus, I feel postgresIsForeignTruncatable() is not needed.

6. XLOG_HEAP_TRUNCATE record is written even for the truncation of a
foreign table. Why is this necessary?

Please give us more time to investigate this.

2021年3月25日(木) 3:47 Fujii Masao <masao.fujii@oss.nttdata.com>:

On 2021/03/13 18:57, Kazutaka Onishi wrote:

I have fixed the patch to pass check-world test. :D

Thanks for updating the patch! Here are some review comments from me.

By default all foreign tables using <filename>postgres_fdw</filename> are assumed
to be updatable. This may be overridden using the following option:

In postgres-fdw.sgml, "and truncatable" should be appended into
the above first description? Also "option" in the second description
should be a plural form "options"?

<command>TRUNCATE</command> is not currently supported for foreign tables.
This implies that if a specified table has any descendant tables that are
foreign, the command will fail.

truncate.sgml should be updated because, for example, it contains
the above descriptions.

+     <literal>frels_extra</literal> is same length with
+     <literal>frels_list</literal>, that delivers extra information of
+     the context where the foreign-tables are truncated.
+    </para>

Don't we need to document the detail information about frels_extra?
Otherwise the developers of FDW would fail to understand how to
handle the frels_extra when trying to make their FDWs support TRUNCATE.

+               relids_extra = lappend_int(relids_extra, (recurse ? 0 : 1));
+                               relids_extra = lappend_int(relids_extra, -1);

postgres_fdw determines whether to specify ONLY or not by checking
whether the passed extra value is zero or not. That is, for example,
using only 0 and 1 for extra values is enough for the purpose. But
ExecuteTruncate() sets three values 0, -1 and 1 as extra ones. Why are
these three values necessary?

With the patch, if both local and foreign tables are specified as
the target tables to truncate, TRUNCATE command tries to truncate
foreign tables after truncating local ones. That is, if "truncatable"
option is set to false or enough permission to truncate is not granted
yet in the foreign server, an error will be thrown after the local tables
are truncated. I don't think this is good order of processings. IMO,
instead, we should check whether foreign tables can be truncated
before any actual truncation operations. For example, we can easily
do that by truncate foreign tables before local ones. Thought?

XLOG_HEAP_TRUNCATE record is written even for the truncation of
a foreign table. Why is this necessary?

Regards,

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

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

Fujii-san,

XLOG_HEAP_TRUNCATE record is written even for the truncation of
a foreign table. Why is this necessary?

Foreign-tables are often used to access local data structure, like
columnar data files
on filesystem, not only remote accesses like postgres_fdw.
In case when we want to implement logical replication on this kind of
foreign-tables,
truncate-command must be delivered to subscriber node - to truncate
its local data.

In case of remote-access FDW drivers, truncate-command on the subscriber-side is
probably waste of cycles, however, only FDW driver and DBA who configured the
foreign-table know whether it is necessary, or not.

How about your opinions?

Best regards,

2021年3月25日(木) 3:47 Fujii Masao <masao.fujii@oss.nttdata.com>:

On 2021/03/13 18:57, Kazutaka Onishi wrote:

I have fixed the patch to pass check-world test. :D

Thanks for updating the patch! Here are some review comments from me.

By default all foreign tables using <filename>postgres_fdw</filename> are assumed
to be updatable. This may be overridden using the following option:

In postgres-fdw.sgml, "and truncatable" should be appended into
the above first description? Also "option" in the second description
should be a plural form "options"?

<command>TRUNCATE</command> is not currently supported for foreign tables.
This implies that if a specified table has any descendant tables that are
foreign, the command will fail.

truncate.sgml should be updated because, for example, it contains
the above descriptions.

+     <literal>frels_extra</literal> is same length with
+     <literal>frels_list</literal>, that delivers extra information of
+     the context where the foreign-tables are truncated.
+    </para>

Don't we need to document the detail information about frels_extra?
Otherwise the developers of FDW would fail to understand how to
handle the frels_extra when trying to make their FDWs support TRUNCATE.

+               relids_extra = lappend_int(relids_extra, (recurse ? 0 : 1));
+                               relids_extra = lappend_int(relids_extra, -1);

postgres_fdw determines whether to specify ONLY or not by checking
whether the passed extra value is zero or not. That is, for example,
using only 0 and 1 for extra values is enough for the purpose. But
ExecuteTruncate() sets three values 0, -1 and 1 as extra ones. Why are
these three values necessary?

With the patch, if both local and foreign tables are specified as
the target tables to truncate, TRUNCATE command tries to truncate
foreign tables after truncating local ones. That is, if "truncatable"
option is set to false or enough permission to truncate is not granted
yet in the foreign server, an error will be thrown after the local tables
are truncated. I don't think this is good order of processings. IMO,
instead, we should check whether foreign tables can be truncated
before any actual truncation operations. For example, we can easily
do that by truncate foreign tables before local ones. Thought?

XLOG_HEAP_TRUNCATE record is written even for the truncation of
a foreign table. Why is this necessary?

Regards,

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

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

On 2021/03/29 9:31, Kohei KaiGai wrote:

Fujii-san,

XLOG_HEAP_TRUNCATE record is written even for the truncation of
a foreign table. Why is this necessary?

Foreign-tables are often used to access local data structure, like
columnar data files
on filesystem, not only remote accesses like postgres_fdw.
In case when we want to implement logical replication on this kind of
foreign-tables,
truncate-command must be delivered to subscriber node - to truncate
its local data.

In case of remote-access FDW drivers, truncate-command on the subscriber-side is
probably waste of cycles, however, only FDW driver and DBA who configured the
foreign-table know whether it is necessary, or not.

How about your opinions?

I understand the motivation of this. But the other DMLs like UPDATE also
do the same thing for foreign tables? That is, when those DML commands
are executed on foreign tables, their changes are WAL-logged in a publisher side,
e.g., for logical replication? If not, it seems strange to allow only TRUNCATE
on foreign tables to be WAL-logged in a publisher side...

Regards,

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