Hi all,
Attached are two patches which in combination make pg_stat_activity
work reliably for us on Windows.

Advertising

The mysterious socket error turned out to be WSAEWOULDBLOCK. Per
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/winsock/winsock/windows_sockets_error_codes_2.asp
, it seems the thing to do is loop and try again. pipe.patch does
that.
pgstat.patch removes the delayed destroy code for backends, databases,
and tables. Database and table entries are cleaned up immediately upon
receipt of the appropriate message.
Both patches were necessary to make pg_stat_activity work reliably.
With no changes, with a connection pool size of 31, under load, we'd
typically see < 5 rows in pg_stat_activity. With pgstat.patch applied,
the number of rows would typically be between 15 and 20. With
pipe.patch also applied, the number of rows in pg_stat_activity was
accurate.
The test server withstood an approximately four hour test stress test
which replays captured Web traffic, but at full blast. The machine was
completely swamped, but there were no socket errors over the test run
(compared to a frequency of once every couple minutes before).
The one remaining problem is that there seems to be a race condition
when installing the temporary stats file on Windows. As we were
monitoring pg_stat_activity during the test run, occasionally we'd get a
response with zero rows. This may not be much of a problem during
normal conditions (the server was completely overloaded and we were
banging away with "Up Arrow", "Enter" watching pg_stat_activity).
What's the best way to do an atomic rename on Windows? Alternatively,
would it make sense to sleep and try again (up to some limit) when
trying to open the stats file on Windows?
Pete