Thread: Postmaster keeps file references to deleted files

Postmaster keeps file references to deleted files

From
"Tzvi Rotshtein"
Date:
Bug reference:      4543
Logged by:          Tzvi R.
Email address:      tzvi.ro@gmail.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.