Skip site navigation (1) Skip section navigation (2)

Re: Deleted WAL files held open by backends in Linux

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Deleted WAL files held open by backends in Linux
Date: 2009-11-30 18:48:10
Message-ID: 7910.1259606890@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
"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

In response to

Responses

pgsql-hackers by date

Next:From: Zdenek KotalaDate: 2009-11-30 18:53:18
Subject: Re: [PATCH] Add solaris path for docbook COLLATEINDEX
Previous:From: Marko TiikkajaDate: 2009-11-30 18:43:51
Subject: Re: Writeable CTE patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group