TRUNCATE on foreign table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
93 messages Options
12345
Reply | Threaded
Open this post in threaded view
|

TRUNCATE on foreign table

Kazutaka Onishi
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.

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

regards,

--
------------------
Kazutaka Onishi

pgsql14-truncate-on-foreign-table.v1.patch (51K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Zhihong Yu
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 <[hidden email]> 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.

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

regards,

--
------------------
Kazutaka Onishi
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

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

regards,



2021年2月7日(日) 2:08 Zhihong Yu <[hidden email]>:
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 <[hidden email]> 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.

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

regards,

--
------------------
Kazutaka Onishi


--
------------------
Kazutaka Onishi


--
------------------
Kazutaka Onishi

pgsql14-truncate-on-foreign-table.v2.patch (52K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Ashutosh Bapat-2
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 <[hidden email]> wrote:

>
> Thank you for your comment! :D
> I have fixed it and attached the revised patch.
>
> regards,
>
>
>
> 2021年2月7日(日) 2:08 Zhihong Yu <[hidden email]>:
>>
>> 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 <[hidden email]> 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.
>>> https://www.postgresql.org/message-id/flat/CAOP8fzb-t3WVNLjGMC%2B4sV4AZa9S%3DMAQ7Q6pQoADMCf_1jp4ew%40mail.gmail.com#3b6c6ff85ff5c722b36c7a09b2dd7165
>>>
>>> I have fixed the patch due to submit it to Commit Fest 2021-03.
>>>
>>> regards,
>>>
>>> --
>>> ------------------
>>> Kazutaka Onishi
>>> ([hidden email])
>
>
>
> --
> ------------------
> Kazutaka Onishi
> ([hidden email])
>
>
> --
> ------------------
> Kazutaka Onishi
> ([hidden email])



--
Best Wishes,
Ashutosh Bapat


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Bharath Rupireddy
On Tue, Feb 9, 2021 at 5:31 PM Ashutosh Bapat
<[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Ashutosh Bapat-2
On Tue, Feb 9, 2021 at 5:49 PM Bharath Rupireddy
<[hidden email]> wrote:

>
> On Tue, Feb 9, 2021 at 5:31 PM Ashutosh Bapat
> <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Kazutaka Onishi
> 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.

> 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.
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Ashutosh Bapat-2
On Tue, Feb 9, 2021 at 7:45 PM Kazutaka Onishi <[hidden email]> 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.
> https://www.postgresql.org/message-id/20200128040346.GC1552%40paquier.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


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Kazutaka Onishi
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?
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Kohei KaiGai-4
In reply to this post by Ashutosh Bapat-2
2021年2月10日(水) 13:55 Ashutosh Bapat <[hidden email]>:

>
> On Tue, Feb 9, 2021 at 7:45 PM Kazutaka Onishi <[hidden email]> 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.
> > https://www.postgresql.org/message-id/20200128040346.GC1552%40paquier.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 <[hidden email]>


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Ashutosh Bapat-2
In reply to this post by Kazutaka Onishi
On Wed, Feb 10, 2021 at 10:58 PM Kazutaka Onishi <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Ibrar Ahmed-4


On Thu, Feb 11, 2021 at 6:23 PM Ashutosh Bapat <[hidden email]> wrote:
On Wed, Feb 10, 2021 at 10:58 PM Kazutaka Onishi <[hidden email]> 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. 

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

pgsql14-truncate-on-foreign-table.v3.patch (52K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Amit Langote
On Tue, Mar 9, 2021 at 2:24 AM Ibrar Ahmed <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Kazutaka Onishi
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 <[hidden email]>:
On Tue, Mar 9, 2021 at 2:24 AM Ibrar Ahmed <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

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

2021年3月13日(土) 12:35 Kazutaka Onishi <[hidden email]>:
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 <[hidden email]>:
On Tue, Mar 9, 2021 at 2:24 AM Ibrar Ahmed <[hidden email]> 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

pgsql14-truncate-on-foreign-table.v4.patch (52K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Fujii Masao-4


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


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Kazutaka Onishi
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:
https://www.postgresql.org/message-id/CAOP8fzb-t3WVNLjGMC%2B4sV4AZa9S%3DMAQ7Q6pQoADMCf_1jp4ew%40mail.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 <[hidden email]>:

>
>
>
> 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

pgsql14-truncate-on-foreign-table.v5.patch (852K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Kohei KaiGai-4
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 <[hidden email]>:

>
> 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:
> https://www.postgresql.org/message-id/CAOP8fzb-t3WVNLjGMC%2B4sV4AZa9S%3DMAQ7Q6pQoADMCf_1jp4ew%40mail.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 <[hidden email]>:
> >
> >
> >
> > 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 <[hidden email]>


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Kohei KaiGai-4
In reply to this post by Fujii Masao-4
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 <[hidden email]>:

>
>
>
> 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 <[hidden email]>


Reply | Threaded
Open this post in threaded view
|

Re: TRUNCATE on foreign table

Fujii Masao-4


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


12345