BUG #15427: DROP INDEX did not free up disk space - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15427
Logged by:          Andrew P
Email address:      ap@zip.com.au
PostgreSQL version: 10.5
Operating system:   Debian Sarge (9)
Description:

Performed a DROP INDEX to free up disk space but, whilst the command
succeeded the disk space was not freed up.

The index was:

CREATE INDEX radlelink_rlreid_idx ON radlelink (rlid, reid);

Config of table:

CREATE TABLE radlelink (
        rlid            BIGINT REFERENCES radlogins (id) ON DELETE
CASCADE,
        reid            BIGINT REFERENCES radextra (id) ON DELETE CASCADE
);

Ran 'lsof -nP +L1' on the server as per suggestion on postgresql irc channel
and this was the (abridged output):

COMMAND   PID     USER   FD   TYPE DEVICE   SIZE/OFF NLINK    NODE NAME
postgres 2633 postgres  197u   REG  252,5   16777216     0 3410028
/var/lib/postgresql/10/main/pg_wal/00000001000003960000006E (deleted)
postgres 2633 postgres  325u   REG  252,5          0     0 3409445
/var/lib/postgresql/10/main/base/16562/17437 (deleted)
postgres 2633 postgres  326u   REG  252,5 1073741824     0 3409702
/var/lib/postgresql/10/main/base/16562/17437.1 (deleted)
postgres 2633 postgres  327u   REG  252,5 1073741824     0 3409704
/var/lib/postgresql/10/main/base/16562/17437.2 (deleted)
postgres 2633 postgres  328u   REG  252,5 1073741824     0 3409705
/var/lib/postgresql/10/main/base/16562/17437.3 (deleted)
postgres 2633 postgres  329u   REG  252,5 1073741824     0 3409452
/var/lib/postgresql/10/main/base/16562/17437.4 (deleted)
postgres 2633 postgres  330u   REG  252,5 1073741824     0 3409709
/var/lib/postgresql/10/main/base/16562/17437.5 (deleted)
postgres 2633 postgres  331u   REG  252,5 1073741824     0 3409710
/var/lib/postgresql/10/main/base/16562/17437.6 (deleted)
postgres 2633 postgres  332u   REG  252,5 1073741824     0 3409706
/var/lib/postgresql/10/main/base/16562/17437.7 (deleted)
postgres 2633 postgres  333u   REG  252,5 1073741824     0 3409722
/var/lib/postgresql/10/main/base/16562/17437.8 (deleted)
postgres 2633 postgres  334u   REG  252,5 1073741824     0 3409724
/var/lib/postgresql/10/main/base/16562/17437.9 (deleted)
postgres 2633 postgres  335u   REG  252,5 1073741824     0 3409716
/var/lib/postgresql/10/main/base/16562/17437.10 (deleted)
...
postgres 2633 postgres  403u   REG  252,5 1073741824     0 3409970
/var/lib/postgresql/10/main/base/16562/17437.78 (deleted)
postgres 2633 postgres  404u   REG  252,5 1073741824     0 3409976
/var/lib/postgresql/10/main/base/16562/17437.79 (deleted)
postgres 2633 postgres  405u   REG  252,5 1073741824     0 3409969
/var/lib/postgresql/10/main/base/16562/17437.80 (deleted)
postgres 2633 postgres  406u   REG  252,5 1073741824     0 3409983
/var/lib/postgresql/10/main/base/16562/17437.81 (deleted)
postgres 2633 postgres  407u   REG  252,5 1073741824     0 3409984
/var/lib/postgresql/10/main/base/16562/17437.82 (deleted)
postgres 2633 postgres  408u   REG  252,5 1073741824     0 3409981
/var/lib/postgresql/10/main/base/16562/17437.83 (deleted)
postgres 2633 postgres  409u   REG  252,5 1073741824     0 3410002
/var/lib/postgresql/10/main/base/16562/17437.84 (deleted)
postgres 2633 postgres  410u   REG  252,5 1073741824     0 3410012
/var/lib/postgresql/10/main/base/16562/17437.85 (deleted)
postgres 2633 postgres  411u   REG  252,5 1073741824     0 3409991
/var/lib/postgresql/10/main/base/16562/17437.86 (deleted)
postgres 2633 postgres  412u   REG  252,5 1073741824     0 3410025
/var/lib/postgresql/10/main/base/16562/17437.87 (deleted)
postgres 2633 postgres  413u   REG  252,5  264241152     0 3410026
/var/lib/postgresql/10/main/base/16562/17437.88 (deleted)

PID 2633 was:
postgres  2633 12.5  0.4 305868 158632 ?       Ss   Oct09 544:04 postgres:
10/main: postgres radlogs [local] idle

I executed 'SELECT 1;' in that session (it was a psql session) and 5s later
it returned, the files were closed and the disk space freed.

There were also two autovacuum processes up at the time:
postgres 29102  2.4  0.2 355320 76724 ?        Ss   13:09   0:45 postgres:
10/main: autovacuum worker process   radlogs
postgres 29378 16.0  0.1 355312 37976 ?        Ss   13:40   0:00 postgres:
10/main: autovacuum worker process   radlogs

Hope this helps.

AP