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

From: Jamison, Kirk [mailto:k(dot)jamison(at)jp(dot)fujitsu(dot)com]
> >On Thu, Nov 15, 2018 at 2:30 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
> 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