Postmaster keeps file references to deleted files

Bug reference: 4543
Logged by: Tzvi R.
Email address: tzvi(dot)ro(at)gmail(dot)com
PostgreSQL version: 8.3.5
Operating system: Linux (kernel 2.6.18)
Description: File references are still held to deleted files
Details:

Hi,

We frequently create large work tables that span across multiple files
(though not using the "create temporary table" syntax).

I noticed a few days ago that some transactions are about to fill the mount
completely with those work tables, so I started canceling those queries and
then dropped the tables, however, not all space has been reclaimed
afterwards.
Investigating further (using lsof) showed that postmaster still holds the
deleted files open, whereas I would expect it to close them prior to
deleting them (the database was completely idle, there were no running
transactions/rollbacks at that time). Disk space was eventually reclaimed
when the relevant process got terminated.

Effectively, this prevents from the space being freed and results in leaks
that cannot be reclaimed for other uses.

Here's a quick list of what we've got from lsof showing this behavior:

ERROR postmaste 10103 postgres 249u REG 120,81
0 524289 /db/pg_sys/data/base/30784/5953625 (deleted)
ERROR postmaste 10103 postgres 250u REG 120,81
1073741824 524290 /db/pg_sys/data/base/30784/5953625.1 (deleted)
ERROR postmaste 10103 postgres 251u REG 120,81
1073741824 524293 /db/pg_sys/data/base/30784/5953625.2 (deleted)
ERROR postmaste 10103 postgres 252u REG 120,81
1073741824 524294 /db/pg_sys/data/base/30784/5953625.3 (deleted)
ERROR postmaste 10103 postgres 255u REG 120,81
1073741824 524295 /db/pg_sys/data/base/30784/5953625.4 (deleted)
ERROR postmaste 10103 postgres 257u REG 120,81
1073741824 524296 /db/pg_sys/data/base/30784/5953625.5 (deleted)
ERROR postmaste 10103 postgres 258u REG 120,81
1073741824 524297 /db/pg_sys/data/base/30784/5953625.6 (deleted)
ERROR postmaste 10103 postgres 259u REG 120,81
1073741824 524298 /db/pg_sys/data/base/30784/5953625.7 (deleted)
ERROR postmaste 10103 postgres 260u REG 120,81
1073741824 524301 /db/pg_sys/data/base/30784/5953625.8 (deleted)
ERROR postmaste 10103 postgres 261u REG 120,81
1073741824 524311 /db/pg_sys/data/base/30784/5953625.9 (deleted)
ERROR postmaste 10103 postgres 262u REG 120,81
1073741824 524312 /db/pg_sys/data/base/30784/5953625.10 (deleted)
ERROR postmaste 10103 postgres 263u REG 120,81
1073741824 524313 /db/pg_sys/data/base/30784/5953625.11 (deleted)
ERROR postmaste 13308 postgres 196u REG 120,81
0 524289 /db/pg_sys/data/base/30784/5953625 (deleted)
ERROR postmaste 13308 postgres 198u REG 120,81
1073741824 524290 /db/pg_sys/data/base/30784/5953625.1 (deleted)
ERROR postmaste 13308 postgres 199u REG 120,81
1073741824 524295 /db/pg_sys/data/base/30784/5953625.4 (deleted)
ERROR postmaste 13308 postgres 200u REG 120,81
1073741824 524296 /db/pg_sys/data/base/30784/5953625.5 (deleted)
ERROR postmaste 13308 postgres 201u REG 120,81
1073741824 524297 /db/pg_sys/data/base/30784/5953625.6 (deleted)
ERROR postmaste 13308 postgres 203u REG 120,81
1073741824 524298 /db/pg_sys/data/base/30784/5953625.7 (deleted)
ERROR postmaste 13308 postgres 204u REG 120,81
1073741824 524301 /db/pg_sys/data/base/30784/5953625.8 (deleted)
ERROR postmaste 13308 postgres 205u REG 120,81
1073741824 524311 /db/pg_sys/data/base/30784/5953625.9 (deleted)
ERROR postmaste 13308 postgres 206u REG 120,81
1073741824 524293 /db/pg_sys/data/base/30784/5953625.2 (deleted)
ERROR postmaste 13308 postgres 207u REG 120,81
1073741824 524294 /db/pg_sys/data/base/30784/5953625.3 (deleted)
ERROR postmaste 13308 postgres 208u REG 120,81
1073741824 524312 /db/pg_sys/data/base/30784/5953625.10 (deleted)
ERROR postmaste 13308 postgres 209u REG 120,81
1073741824 524313 /db/pg_sys/data/base/30784/5953625.11 (deleted)
ERROR postmaste 14583 postgres 78u REG 120,81
0 3751985 /db/pg_sys/data/base/31294/5953974 (deleted)
ERROR postmaste 14583 postgres 79u REG 120,81
1073741824 3751983 /db/pg_sys/data/base/31294/5953974.1 (deleted)
ERROR postmaste 14583 postgres 80u REG 120,81
1073741824 3751994 /db/pg_sys/data/base/31294/5953974.2 (deleted)
ERROR postmaste 14583 postgres 81u REG 120,81
1073741824 3752005 /db/pg_sys/data/base/31294/5953974.3 (deleted)
ERROR postmaste 14583 postgres 82u REG 120,81
1073741824 3751995 /db/pg_sys/data/base/31294/5953974.4 (deleted)
ERROR postmaste 14583 postgres 83u REG 120,81
0 3932304 /db/pg_sys/data/base/31549/5953977 (deleted)
ERROR postmaste 14583 postgres 87u REG 120,81
1073741824 3932416 /db/pg_sys/data/base/31549/5953977.1 (deleted)
ERROR postmaste 14583 postgres 88u REG 120,81
1073741824 3932419 /db/pg_sys/data/base/31549/5953977.2 (deleted)
ERROR postmaste 14583 postgres 89u REG 120,81
1073741824 3932477 /db/pg_sys/data/base/31549/5953977.3 (deleted)
ERROR postmaste 14583 postgres 91u REG 120,81
1073741824 3932943 /db/pg_sys/data/base/31549/5953977.4 (deleted)
ERROR postmaste 14583 postgres 92u REG 120,81
0 3784999 /db/pg_sys/data/base/31804/5953980 (deleted)
ERROR postmaste 14583 postgres 93u REG 120,81
1073741824 3785346 /db/pg_sys/data/base/31804/5953980.1 (deleted)
ERROR postmaste 14583 postgres 95u REG 120,81
0 3964941 /db/pg_sys/data/base/32059/5953983 (deleted)
ERROR postmaste 14583 postgres 96u REG 120,81
1073741824 6324225 /db/pg_sys/data/base/32059/5953983.1 (deleted)
ERROR postmaste 15023 postgres 191u REG 120,81
0 3751985 /db/pg_sys/data/base/31294/5953974 (deleted)
ERROR postmaste 15023 postgres 195u REG 120,81
1073741824 3751983 /db/pg_sys/data/base/31294/5953974.1 (deleted)
ERROR postmaste 15023 postgres 196u REG 120,81
1073741824 3751994 /db/pg_sys/data/base/31294/5953974.2 (deleted)

Thanks,
Tzvi.