Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1kSh6Z-0000EM-0N
	for pgsql-bugs@arkaria.postgresql.org; Wed, 14 Oct 2020 13:46:11 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1kSh6X-0006Lw-Rc
	for pgsql-bugs@arkaria.postgresql.org; Wed, 14 Oct 2020 13:46:09 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <denis.patron@previnet.it>)
	id 1kSaZY-0007rZ-UC
	for pgsql-bugs@lists.postgresql.org; Wed, 14 Oct 2020 06:47:40 +0000
Received: from n3.nabble.com ([162.255.23.22])
	by magus.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <denis.patron@previnet.it>)
	id 1kSaZU-00056K-Qz
	for pgsql-bugs@postgresql.org; Wed, 14 Oct 2020 06:47:40 +0000
Received: from n3.nabble.com (localhost [127.0.0.1])
	by n3.nabble.com (Postfix) with ESMTP id E117D21E01F63
	for <pgsql-bugs@postgresql.org>; Tue, 13 Oct 2020 23:47:34 -0700 (MST)
Date: Tue, 13 Oct 2020 23:47:34 -0700 (MST)
From: "denis.patron" <denis.patron@previnet.it>
To: pgsql-bugs@postgresql.org
Message-ID: <1602658054887-0.post@n3.nabble.com>
In-Reply-To: <20201014043540.6etouknbpqqiqj7t@alap3.anarazel.de>
References: <16663-fe97ccf9932fc800@postgresql.org>
 <20201014.120510.184391841712786310.horikyota.ntt@gmail.com>
 <20201014043540.6etouknbpqqiqj7t@alap3.anarazel.de>
Subject: Re: BUG #16663: DROP INDEX did not free up disk space: idle
 connection hold file marked as deleted
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Precedence: bulk

Andres Freund wrote
> 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 &lt;

> noreply@

> &gt; wrote in
>> > The following bug has been logged on the website:
>> >
>> > Bug reference:      16663
>> > Logged by:          Denis Patron
>> > Email address:      

> denis.patron@

>> > 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



thanks for replying.
the problem is that I have a very large database, with indexes of up to 70
Gb. while I redo the indexes in concurrently mode, if an idle transaction is
using the index in question, the segment file (<id> _1 <id> _2 etc) of the
index remains in the filesystem (marked as deleted) as long as the idle
connection that it is blocking it does not make another transaction. this
means that I can have hundreds of GB of space occupied by files marked
"deleted", and this for hours. the risk is to run out of free space



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html