Re: BUG #16663: DROP INDEX did not free up disk space: idle connection hold file marked as deleted

Hi,

On 2020-10-14 12:05:10 +0900, Kyotaro Horiguchi wrote:
> This is not a bug.
>
> At Fri, 09 Oct 2020 13:24:15 +0000, PG Bug reporting form <noreply@postgresql.org> wrote in
> > The following bug has been logged on the website:
> >
> > Bug reference:      16663
> > Logged by:          Denis Patron
> > Email address:      denis.patron@previnet.it
> > PostgreSQL version: 11.9
> > Operating system:   CentOS 7
> > Description:
> >
> > I have an index, which at the file system level, is made up of multiple
> > segments (file: <id>.1, <id>.2 ecc). When I DROP INDEX, the index is dropped
> > in Postgresql but at the file system level, the segments are marked as
> > "deleted". if I check with the lsof command, I see that the segments are in
> > use from an idle connection. This does not happen if the index is formed by
> > only one segment (in my case <1Gb). How can I prevent this?
> > thanks
>
> That references to deleted files will dissapear at the beginning of
> the next transaction.
>
> At the time a relation including an index is dropped, the first
> segment file (named as "<id>" without a suffix number) is left behind
> so the file is not shown as "(deleted)" in lsof output.

I think we should consider either occasionally sending a sinval catchup
interrupt to backends that have been idle for a while, or to use a timer
that we use to limit the maximum time until we process sinvals. Just
having to wait till all backends become busy and process sinval events
doesn't really seem like good approach to me.

Regards,

Andres