Re: Postmaster holding unlinked files for pg_largeobject table

Excerpts from Tom Lane's message of sáb jun 04 12:49:05 -0400 2011:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > What surprises me is that the open references remain after a database
> > drop.  Surely this means that no backends keep open file descriptors to
> > any table in that database, because there are no connections.
> 
> bgwriter ...

Actually you were both wrong, hah.  It's not bgwriter, and this doesn't
belong on pgsql-general.  It's a backend.  However, as we mentioned
initially, the database to which this file belongs is dropped.

What we found out after more careful investigation is that the file is
kept open by a backend connected to a different database.  I have a
suspicion that what happened here is that this backend was forced to
flush out a page from shared buffers to read some other page; and it was
forced to do a fsync of this file.  And then it forgets to close the
file descriptor.

Actually, there are 11 processes holding open file descriptors to the
table, each to a slightly different subset of the many segments of the
table.  (There's also one holding a FD to the deleted
pg_largeobject_loid_pn_index -- remember, this is a dropped database).
All those backends belong to Zabbix, the monitoring platform, which are
connected to a different database.

I think what we have here is a new bug.

(This is running 8.4.8, by the way.)

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support