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. > > 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. Thanks for the feedback. Is the planner caching the plan even in case of VOLATILE functions? The DO construct executes perfectly with no apparent caching so I was excluding any plan caching. I was also thinking about using the catalog, but it looked to me "easier" my way. And, of course, if you have better advises for a "session variables" solution, my ears are open. Thanks again, Tom. -- 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
