Re: Deleted WAL files held open by backends in Linux

"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