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(at)postgresql(dot)org> wrote in
> The following bug has been logged on the website:
>
> Bug reference: 16663
> Logged by: Denis Patron
> Email address: denis(dot)patron(at)previnet(dot)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