Search Postgresql Archives

Re: [9.0] On temporary tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

but if you need a session variables, then you can use a plperl

http://www.postgresql.org/docs/9.0/static/plperl-global.html

Regards

Pavel Stehule

> 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.
>
> Â Â Â Â Â Â Â Â Â Â Â Âregards, tom lane
>
> --
> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux