Hello 2010/9/30 Vincenzo Romano <vincenzo.romano@xxxxxxxxxxx>: > 2010/9/30 Pavel Stehule <pavel.stehule@xxxxxxxxx>: >> Hello >> >> 2010/9/30 Tom Lane <tgl@xxxxxxxxxxxxx>: >>> Vincenzo Romano <vincenzo.romano@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
