Re: global temporary tables

On Apr 24, 2010, at 8:14 PM, Robert Haas wrote:
>> One possibility: rename the existing pg_stats to pg_stats_permanent. Create a global temporary table called pg_stats_temporary. pg_stats becomes a union of the two. I know the backend wouldn't be able to use the view, but hopefully access to statistics goes through a limited set of functions so that teaching them about the two different tables isn't hard.
> 
> Yeah, I don't think that would be too horrible.  Part of me feels like
> you'd want to have the ability to store stats for a global temp table
> in either one of those tables depending on use-case, but I'm also
> reluctant to invent a lot of new syntax for a very limited use case.

Yeah, I'm thinking that's very probably overkill. And if we were going to go to that level, I think it would be far more useful to provide an interface to allow manual control over statistics first, so that you can give the optimizer custom information.

>> As for cleanup and storage questions; what about having temp objects live in pgsql_tmp? I'm thinking create a directory under pgsql_tmp for a backend PID the first time it creates a temp object (global or local) and create the files in there. That also means that we don't have to come up with different relfilenodes for each backend.
> 
> That would impose a couple of implementation restrictions that don't
> seem necessary.  One, it would imply ignoring reltablespace.  Two, it
> would prohibit (or at least complicate) allowing a backend to CLUSTER
> or REINDEX its own private copy of the rel.

Well, the same structure could be imposed underneath a temptablespace. I don't think it matters where you ultimately put it, the goal is just to make sure you can definitively tell that a file was a: temporary and b: what PID it belonged to. That allows for safe cleanup.
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net