Re: [9.0] On temporary tables

2010/9/30 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> writes:
>> create or replace function session_init()
>> returns void
>> language plpgsql
>> as $body$
>> declare
>>   t text;
>> begin
>>   select valu into t from session where name='SESSION_ID';
>>   if not found then
>>     create temporary table session ( like public.session including all );
>>     insert into session values ( 'SESSION_ID',current_user );
>>   end if;
>> end;
>> $body$;
>
>> The idea is to create a temporary table to store session variables
>> only of there's no temporary table with that name.
>
> That isn't going to work tremendously well.  plpgsql will cache a plan
> for that SELECT on first use, and creation of the temp table is not an
> event that will cause replanning of a select that doesn't already use
> the temp table.
>
> If you're dead set on this design (which frankly doesn't seem like a
> terribly great idea to me), try doing the initial probe with an EXECUTE
> so it'll be replanned each time.
>
> Or you might try examining the system catalogs directly rather than
> relying on an attempted table access, eg
>
>        if not exists (select 1 from pg_catalog where relname =
>                       'session' and pg_table_is_visible(oid))
>        then ... create it ...
>
> That approach would work best if you *didn't* have any permanent
> table that the temp tables were masking, which on the whole seems
> like a smarter plan to me.

Thanks for the feedback.

Is the planner caching the plan even in case of VOLATILE functions?
The DO construct executes perfectly with no apparent caching so
I was excluding any plan caching.

I was also thinking about using the catalog, but it looked to me
"easier" my way.
And, of course, if you have better advises for a "session variables"
solution, my ears are open.

Thanks again, Tom.

--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS