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:
>> A backend would never open a WAL file unless it had to write a WAL
>> record, so I'm having a hard time believing that these were
>> totally read-only transactions.  Can you give specifics?

> You will note that the connections logged in as "viewer" (and only
> those) are holding open a deleted WAL file.  This user has not been
> granted anything except SELECT permissions to any tables.

You sure it's not creating any temp tables?  You didn't mention
revoking TEMP privilege.

I can think of one code path that could result in a genuinely read-only
session having to write WAL: if it's forced to flush dirty buffers in
order to read in other pages, and such a buffer was dirtied by
as-yet-uncommitted transactions, it might have to flush WAL to be
allowed to write the dirty buffer.  But I think you'd have had to dial
back the bgwriter to the point of uselessness before this would be a
common occurrence.

> At a minimum, we should add the extra 16MB per connection that might
> be taken on the WAL file system to the calculations people should do
> when sizing that, just in case someone is trying to cut that thin
> while planning on using a lot of connections.

In the first place, this is a complete non-issue except on Windows ---
on other platforms we can rename and recycle the files even if they're
being held open.  I rather doubt anyone would think they could predict a
Windows machine's disk usage that accurately anyway.  In the second
place, for each backend to be holding open a different dead WAL file
strains the limits of credulity.  Telling people to assume 16MB *
max_connections would be a gross overestimate.

			regards, tom lane