Re: Deleted WAL files held open by backends in Linux

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> I ran across the subject issue and spent some time puzzling over it.
> ...
> I'm not sure whether Tom's comment that "There is zero hope of
> making that work." referred to the idea that we could close deleted
> WAL files or to something else. Is a fix feasible?

The reason I was dismissive of that is this scenario: a backend is told
to execute a data-modifying transaction (so it has to write and commit
some WAL records) and then the client promptly goes to sleep and stays
that way for awhile. The backend has nothing to do either, so it's just
sitting waiting on the client socket. There is noplace reasonable here
to make it decide to close its open WAL file. We could possibly do a
pre-emptive close() after transaction commit, but that would result in a
useless distributed overhead of opening and closing the current WAL file
when a backend is engaged in a series of transactions.

On Linux this simply isn't an issue, anyway --- if you can't afford one
open file descriptor per backend for WAL, you're going to have a far
bigger problem with the backend's habit of caching descriptors for data
files. On Windows it might be a problem, but I think we have got
workarounds for all the important cases. The important point is that
we want any overhead for this to be pushed into non-performance-critical
code paths.

> It seemed strange that the only backends which were holding open
> deleted WAL files were ones where the connection was established
> with a login which has no write permissions.

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?

regards, tom lane