Re: Deleted WAL files held open by backends in Linux

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: 
>> You sure it's not creating any temp tables?  You didn't mention
>> revoking TEMP privilege.
 
> They have not been revoked, but I am sure the software publisher
> doesn't explicitly create any, and I'd be very surprised if the
> monitoring software did.  The tables are small enough that it's hard
> to believe that the 50MB work_mem would spill to disk, either (if
> that matters).

It's not about the size of a temp table, because writes to the temp
table itself aren't WAL-logged.  However, the system catalog entries for
a temp table *are* WAL-logged.

> Pretty much every read only JDBC connection seems to be holding open
> a deleted WAL file on my Linux box, but it would take pretty
> pessimal timing for each connection to be holding open a different
> one -- I see that many connections share a deleted WAL file.

This still seems a bit improbable to me.  There has to be something
causing those sessions to touch WAL, and the dirty-buffer scenario
doesn't seem reliable enough.

[ thinks... ]  How about SELECT FOR UPDATE or SELECT FOR SHARE?
Those cause WAL writes.

			regards, tom lane