Re: including PID or backend ID in relpath of temp rels

On Mon, May 17, 2010 at 2:10 PM, Jim Nasby <decibel@decibel.org> wrote:
> Any particular reason not to use directories to help organize things? IE:
>
> base/database_oid/pg_temp_rels/backend_pid/relfilenode
>
> Perhaps relfilenode should be something else.
>
> This seems to have several advantages:
>
> 1: It's more organized. If you want to see all the files for a single backend you have just one place to look.
Findingeverything is still easy via filesystem find. 
> 2: Cleanup becomes easier. When a backend exits, it's entire directory goes away. On server start, everything under
pg_temp_relsgoes away. Unfortunately we still have a race condition with cleaning up if a backend dies and can't run
it'sown cleanup, though I think that anytime that happens we're going to restart everything anyway. 
> 3: It separates all the temporary stuff away from real files.
>
> The only downside I see is some extra code to create the backend_pid directory.

I thought this was a good idea when you first proposed it, but on
further review I've changed my mind.  There are several places in the
code that rely on checking whether the database directory within any
given tablespace is empty to determine whether that database is using
that tablespace.  While I could rewrite all of that logic to do the
right thing, I think it's unnecessary pain.

I talked with Tom Lane about this a little bit at PGcon and opined
that we probably only need to clean out stray temporary files at
startup.  So what I'm tempted to do is just write a function that goes
through all tablespace/database combinations and scans each directory
for files with a name like t<digits>_<digits> and blows them away.
This will leave the catalog entries pointing at nothing, but we
already have working code in autovacuum.c to clean up the catalog
entries, and I believe that will work just fine even if the underlying
files have been removed earlier.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company