Hi!
It's assumed in PostgreSQL codebase that pgrename atomically replaces target file with source file even if target file is open and being read by another process. And this assumption is true on Linux, but it's false on Windows. MoveFileEx() triggers an error when target file is open (and accordingly locked). Some our customers has been faced such errors while operating heavily loaded PostgreSQL instance on Windows.
LOG could not rename temporary statistics file "pg_stat_tmp/global.tmp" to "pg_stat_tmp/global.stat": Permission denied
I've managed to reproduce this situation. Reliable reproducing of this issue required patch to PostgreSQL core. I've written slow-read-statfiles.patch for artificial slowdown of pgstat_read_statsfiles() – sleep 100 ms after each statfile entry. If you run make-100-dbs.sql on patched version, and then few times execute
select pg_stat_get_tuples_inserted('t1'::regclass);
in psql, then you would likely get the error above on Windows.
Attached patch atomic-pgrename-windows-1.patch fixes this problem. It appears to be possible to atomically replace file on Windows – ReplaceFile() does that. ReplaceFiles() requires target file to exist, this is why we still need to call MoveFileEx() when it doesn't exist.
This patch is based on work of Victor Spirin who was asked by Postgres Pro to research this problem.
