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

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