Re: TRUNCATE on foreign table

From: Kohei KaiGai <kaigai(at)heterodb(dot)com>
To: Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
Cc: Kazutaka Onishi <onishi(at)heterodb(dot)com>, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>, Zhihong Yu <zyu(at)yugabyte(dot)com>, PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: TRUNCATE on foreign table
Date: 2021-02-11 00:39:56
Message-ID: CAOP8fzYC0kkJL-vL+cN8Bk96gAX8=Bx2g663br3+dRd8GJLciw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

2021年2月10日(水) 13:55 Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>:
>
> On Tue, Feb 9, 2021 at 7:45 PM Kazutaka Onishi <onishi(at)heterodb(dot)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.
> > 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 <kaigai(at)heterodb(dot)com>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bossart, Nathan 2021-02-11 01:27:16 Re: partial heap only tuples
Previous Message Justin Pryzby 2021-02-10 23:55:58 Re: doc review for v14