RE: reloption to prevent VACUUM from truncating empty pages at theend of relation

From: Jamison, Kirk [mailto:k.jamison@jp.fujitsu.com]
> >On Thu, Nov 15, 2018 at 2:30 PM Alvaro Herrera 
> wrote:
> >>
> >> On 2018-Nov-15, Laurenz Albe wrote:
> >>
> > > > This new option would not only mitigate the long shared_buffers
> > > > scan, it would also get rid of the replication conflict caused by
> > > > the AccessExclusiveLock taken during truncation, which is discussed
> > > > in
> > > >
> https://www.postgresql.org/message-id/c9374921e50a5e8fb1ecf04eb8c6eb
> > > > c3%40postgrespro.ru and seems to be a more difficult problem than
> > > > anticipated.
> > >
> > > FWIW I was just reminded yesterday that the AEL-for-truncation has
> > > been diagnosed to be a severe problem in production, and with no other
> > > solution in sight, I propose to move forward with the stop-gap.
> 
> I just want to ask whether or not we could proceed with this approach for
> now and
> if it is possible that we could have this solution integrated before PG12
> development ends?

As most people seem to agree adding the reloption, here's the patch.  It passes make check, and works like this:

postgres=# CREATE TABLE a (c int) WITH (shrink_enabled  = off);
postgres=# INSERT INTO a VALUES(1);
postgres=# DELETE FROM a;
postgres=# SELECT pg_relation_size('a');
 pg_relation_size
------------------
             8192
(1 row)

postgres=# VACUUM a;
postgres=# SELECT pg_relation_size('a');
 pg_relation_size
------------------
             8192
(1 row)

postgres=#


As Tom said, we want to shorten the shared buffer scan during table truncation as a separate undertaking.  Kirk will do it for PG 13.  I'd appreciate much help from many people, because I'm afraid it will be very dificult.

And Tom mentioned likewise, I recognize I have to refresh my memory for fixing the data corruption by failed TRUNCATE...


Regards
Takayuki Tsunakawa