Re: [9.0] On temporary tables

2010/9/30 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello
>
> 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.
>>
>
> I found a little bit faster solution a catching a exception.
>
> http://okbob.blogspot.com/2008/11/plpgsql-and-temp-tables.html

Hmmm ... do you think the performance would be the same in v9?

> but if you need a session variables, then you can use a plperl
>
> http://www.postgresql.org/docs/9.0/static/plperl-global.html

I will look into this. What I need is a set of variable for each connection.

> Regards
>
> Pavel Stehule

Anyway, I'm quite puzzled by the fact that an EXCEPTION WHEN can be
faster than an IF .. THEN .. ELSE
with a rather simple test. Unless the pg_table_is_visible() is really bad.

Thanks a lot.

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