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

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.

The next checkpoint removes the first segment.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center