On Apr 24, 2010, at 12:31 PM, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> At least AIUI, the use case for this feature is that you want to avoid
>> creating "the same" temporary table over and over again.
>
> The context that I've seen it come up in is that people don't want to
> clutter their functions with create-it-if-it-doesn't-exist logic,
> which you have to have given the current behavior of temp tables.
> Any performance gain from reduced catalog churn would be gravy.
>
> Aside from the DROP problem, I think this implementation proposal
> has one other big shortcoming: what are you going to do about
> table statistics? In many cases, you really *have* to do an ANALYZE
> once you've populated a temp table, if you want to get decent plans
> for it. Where will you put those stats?
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
hopefullyaccess to statistics goes through a limited set of functions so that teaching them about the two different
tablesisn't hard.
As for cleanup and storage questions; what about having temp objects live in pgsql_tmp? I'm thinking create a directory
underpgsql_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. On the other hand,
somelayer (presumably smgr) would need to understand whether a relation was temporary or not. If we do that, cleanup is
easy:you can remove any directories that no longer have a running PID. For forensics you probably only want to do that
automaticallywhen a backend starts and discovers it already has a directory, though we should also provide an
administratorfunction that will clobber all directories that no longer have backends.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net