Re: Patch to remove sort files, temp tables, unreferenced files

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> We can't just throw up our hands and say we can't account for this
> left-over stuff. We have to come up with some solution.

No we don't. I'm not convinced that we have a problem at all anymore.
(Show me field reports from 7.1; older versions are irrelevant to this
discussion.) But I am convinced that a half-baked solution would be
worse than not doing anything.

>> 1. Removing sorttemp files during vacuum is not safe; you cannot know
>> that they don't belong to any running backend. (No, the fact that you
>> looked in PROC and didn't find the PID doesn't prove anything; the same
>> PID could have been reassigned to a new backend since you looked.)

> But I have the directory entry before I look for the PID. Seems safe.

No. PIDs wrap around, therefore sorttemp file names recycle. You might
be able to establish that the backend that originally created the file
is gone, but it's still possible that by the time you actually do the
unlink, you are cutting the knees off a new backend that has re-used the
file name.

Basically, you cannot do any of this safely without a lock. If you
think you can, then you don't understand the problem.

I don't think there are any suitable locks in the system at the moment,
and in any case I don't like the loss of concurrency that would occur
if we interlocked process start/stop, sorttemp file
creation/destruction, etc, to the extent that would be needed to make
the world safe for on-line temp file removal. This cure looks much
worse than the disease to me...

>> BTW, while I approve of moving sorttemp files into their own
>> subdirectory, it's sheer folly to give them names therein that look
>> so much like regular relation file names. They should still be named
>> something like "sorttempNNN".

> They are in their own directory. Naming as backend_pid.serial number seems
> OK to me.

The code won't get confused, but humans might. Keep in mind also that
the reason for having a separate directory is so that a DBA can symlink
the directory to someplace else. What happens if he symlinks it to a
directory that also contains real data files? Far better to make sure
that temp file names cannot conflict with relation file names, whether
they're in the same directory or not.

regards, tom lane