SQLite will open temporary files (for sorting and sundry functions), then immediately unlink() them. This is allowed behavior under POSIX.
The NFS trick was documented in "Why NFS Sucks" by Olaf Kirch.
"Some other cases are a bit stranger. One such
case is the ability to write to an open unlinked
file. POSIX says an application can open a file
for reading and writing, unlink it, and continue
to do I/O on it. The file is not supposed to go
away until the last application closes it.
"This is difficult to do over NFS, since traditionally, the NFS server has no concept of “open”
files (this was added in NFSv4, however). So
when a client removes a file, it will be gone for
good, and the file handle is no longer valid—
and attempt to read from or write to that
file will result in a “Stale file handle” error.
"The way NFS traditionally kludges around this
is by doing what has been dubbed a “silly re-
name.” When the NFS client notices during an
unlink call that one or more applications still
hold an open file descriptor to this file, it will
not send a REMOVE call to the server. Instead,
it will rename the file to some temporary file
name, usually .nfsXXX where XXX is some
hex number. This file will stay around until the
last application closes its open file descriptor,
and only then will the NFS client send the final
REMOVE call to the server that gets rid of this
renamed file.
"This sounds like a rather smart sleight of hand,
and it is—up to a point. First off, this does not work across different clients. But that should
not come as a surprise given the lack of cache
consistency."
https://www.kernel.org/doc/ols/2006/ols2006v2-pages-59-72.pd...