Re: [9.0] On temporary tables

Hello

2010/9/30 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 2010/9/30 Pavel Stehule <pavel.stehule@gmail.com>:
>> Hello
>>
>> 2010/9/30 Tom Lane <tgl@sss.pgh.pa.us>:
>>> Vincenzo Romano <vincenzo.romano@notorand.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?
>

yes I think

>> 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.
>

understand - attention - session variables are nice but problematic
when you use some form of connection pooling

>> 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.
>

can be - the reason is relative simple - exception is raised by
planner - so this doesn't do any real query - and lot of data for
planner are in cache.

Pavel


> Thanks a lot.
>
> --
> Vincenzo Romano at NotOrAnd Information Technologies
> Software Hardware Networking Training Support Security
> --
> NON QVIETIS MARIBVS NAVTA PERITVS
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>