Re: BUG #15427: DROP INDEX did not free up disk space

Hi,

On 2018-10-12 03:27:28 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15427
> Logged by: Andrew P
> Email address: ap(at)zip(dot)com(dot)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.

The problem here is that even though we send a invalidation message to
all backends, there's nothing to force backends to process invalidation
messages in time if they're idle. Sure, at some point, when
independently enough inval messages have been created, we'll send out a
catchup interrupt. But that's not necessarily going to be that soon in
a production database.

ISTM that we need to force catchup interrupts in a few cases, like when
smgr invals for truncation.

Greetings,

Andres Freund