From: denis.patron <denis.patron@previnet.it>
To: pgsql-bugs@postgresql.org
Subject: Re: BUG #16663: DROP INDEX did not free up disk space: idle connection hold file marked as deleted
Date: Tue, 13 Oct 2020 23:47:34 -0700 (MST)
Message-ID: <1602658054887-0.post@n3.nabble.com> (raw)
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>
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 <
> noreply@
> > 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: denis.patron@previnet.it
Subject: Re: BUG #16663: DROP INDEX did not free up disk space: idle connection hold file marked as deleted
In-Reply-To: <1602658054887-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox