I ran across the subject issue and spent some time puzzling over it.
It seemed strange that the only backends which were holding open
deleted WAL files were ones where the connection was established
with a login which has no write permissions. Eventually, I vaguely
recalled there was such an issue discussed in recent months and
found it in the archives. It was this:
http://archives.postgresql.org/pgsql-bugs/2009-09/msg00144.php
As a DBA I feel that it's irritating and a potential time waster.
I guess it is a stretch to imagine that a database would have
enough read-only connections to exhaust resources by holding open
one deleted WAL file each; unless they have, say, 200 such
connections and they're cutting things so close that a wasted 3.2GB
of disk space at the WAL file location will run them out.
I'm not sure whether Tom's comment that "There is zero hope of
making that work." referred to the idea that we could close deleted
WAL files or to something else. Is a fix feasible?
This was observed on:
PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (GCC) 4.1.2 20070115 (SUSE Linux)
SUSE Linux Enterprise Server 10 (x86_64)
VERSION = 10
PATCHLEVEL = 2
Linux version 2.6.16.60-0.39.3-smp (geeko(at)buildhost)
(gcc version 4.1.2 20070115 (SUSE Linux))
#1 SMP Mon May 11 11:46:34 UTC 2009
-Kevin