Re: [9.0] On temporary tables

2010/9/30 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 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.

Quoting from documentation (v9.0.0 at chapter 35.6, v8.4.4 at chapter 34.6)
"A VOLATILE function can do anything, including modifying the
database. It can return different
results on successive calls with the same arguments. The optimizer
makes no assumptions about
the behavior of such functions. A query using a volatile function will
re-evaluate the function at
every row where its value is needed."

So, my question below stands still.

> Is the planner caching the plan even in case of VOLATILE functions?

I was expecting a volatile function not to be planned at definition
time, but rather at
execution time.
Is this assumption wrong?

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