Patch to improve reliability of postgresql on linux nfs - Mailing list pgsql-hackers

Hi Hackers,

I run a number of postgresql installations on NFS and on the whole I find this to be very reliable.  I have however run
intoa few issues when there is concurrent writes from multiple processes. 

I see errors such as the following:

2011-07-31 22:13:35 EST postgres postgres [local] LOG:  connection authorized: user=postgres database=postgres
2011-07-31 22:13:35 EST    ERROR:  could not write block 1 of relation global/2671: wrote only 4096 of 8192 bytes
2011-07-31 22:13:35 EST    HINT:  Check free disk space.
2011-07-31 22:13:35 EST    CONTEXT:  writing block 1 of relation global/2671
2011-07-31 22:13:35 EST [unknown] [unknown]  LOG:  connection received: host=[local]

I have also seen similar errors coming out of the WAL writer, however they occur at the level PANIC, which is a little
moredrastic. 

After spending some time with debug logging turned on and even more time staring at strace, I believe this occurs when
oneprocess was writing to a data file and it received a SIGINT from another process, eg: 
(These logs are from another similar run)

[pid  1804] <... fsync resumed> )       = 0
[pid 10198] kill(1804, SIGINT <unfinished ...>
[pid  1804] lseek(3, 4915200, SEEK_SET) = 4915200
[pid  1804] write(3, "c\320\1\0\1\0\0\0\0\0\0\0\0\0K\2\6\1\0\0\0\0\373B\0\0\0\0\2\0m\0"..., 32768 <unfinished ...>
[pid 10198] <... kill resumed> )        = 0
[pid  1804] <... write resumed> )       = 4096
[pid  1804] --- SIGINT (Interrupt) @ 0 (0) ---
[pid  1804] rt_sigreturn(0x2)           = 4096
[pid  1804] write(2, "\0\0\373\0\f\7\0\0t2011-08-30 20:29:52.999"..., 260) = 260
[pid  1804] rt_sigprocmask(SIG_UNBLOCK, [ABRT],  <unfinished ...>
[pid  1802] <... select resumed> )      = 1 (in [5], left {0, 999000})
[pid  1804] <... rt_sigprocmask resumed> NULL, 8) = 0
[pid  1804] tgkill(1804, 1804, SIGABRT) = 0
[pid  1802] read(5,  <unfinished ...>
[pid  1804] --- SIGABRT (Aborted) @ 0 (0) ---
Process 1804 detached

After finding this, I came up with the following test case which easily replicated our issue:

#!/bin/bash

name=$1
number=1
while true; do
  /usr/bin/psql -c "CREATE USER \"$name$number\" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD
'pass';"
  /usr/bin/createdb -E UNICODE -O $name$number $name$number
  if `grep -q PANIC /data/postgresql/data/pg_log/*`; then
    exit
  fi
  let number=$number+1
done

When I run a single copy of this script, I have no issues, however when I start up a few more copies to simultaneously
hitthe DB, it crashes quiet quickly - usually within 20 or 30 seconds. 

After looking through the code I found that when postgres calls write() it doesn't retry.  In order to address the
issuewith the PANIC in the WAL writer I set the sync method to o_sync which solved the issue in that part of the code,
howeverI was still seeing failures in other areas of the code (such as the FileWrite function).  Following this, I
spoketo an NFS guru who pointed out that writes under linux are not guaranteed to complete unless you open up O_SYNC or
similaron the file handle.  I had a look in the libc docs and found this: 

http://www.gnu.org/s/libc/manual/html_node/I_002fO-Primitives.html

----
The write function writes up to size bytes from buffer to the file with descriptor filedes. The data in buffer is not
necessarilya character string and a null character is output like any other character. 

The return value is the number of bytes actually written. This may be size, but can always be smaller. Your program
shouldalways call write in a loop, iterating until all the data is written. 
----

After finding this, I checked a number of other pieces of software that we see no issues with on NFS (such as the JVM)
fortheir usage of write().  I confirmed they write in a while loop and set about patching the postgres source. 

I have made this patch against 8.4.8 and confirmed that it fixes the issue we see on our systems.  I have also checked
thatmake check still passes.  

As my C is terrible, I would welcome any comments on the implementation of this patch.

Best regards,

George