Date: Wed, 14 Oct 2020 12:05:10 +0900 (JST)
Message-Id: <20201014.120510.184391841712786310.horikyota.ntt@gmail.com>
To: denis.patron@previnet.it, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16663: DROP INDEX did not free up disk space: idle
 connection hold file marked as deleted
From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
In-Reply-To: <16663-fe97ccf9932fc800@postgresql.org>
References: <16663-fe97ccf9932fc800@postgresql.org>
User-Agent: Mew version 6.8 on Emacs 26.1
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

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