Thread: [9.0] On temporary tables
Hi all.
This is my case:
-- begin snippet --
reset search_path;
drop table if exists session cascade;
create table session ( name text primary key, valu text not null );
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$;
SELECT * from session;
SELECT * from session_init();
SELECT * from session;
SELECT * from session_init();
-- end snippet --
The output from the last four queries is:
--
tmp2=# SELECT * from session;
name | valu
------+------
(0 rows)
tmp2=# SELECT * from session_init();
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"session_pkey" for table "session"
CONTEXT: SQL statement "create temporary table session ( like
public.session including all )"
PL/pgSQL function "session_init" line 6 at istruzione SQL
session_init
--------------
(1 row)
tmp2=# SELECT * from session;
name | valu
------------+------
SESSION_ID | enzo
(1 row)
tmp2=# SELECT * from session_init();
ERROR: relation "session" already exists
CONTEXT: SQL statement "create temporary table session ( like
public.session including all )"
PL/pgSQL function "session_init" line 6 at istruzione SQL
--
This means that the "if not found then" in the function body didn't work well.
The idea is to create a temporary table to store session variables
only of there's no temporary table with that name.
Any hint on this?
--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS
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.
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
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. > > 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
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 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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? > 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
On 9/30/2010 8:52 AM, Vincenzo Romano wrote: > > 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. More of a question: why use temp tables at all? What does that offer that a single, regular, session table does not? -Andy
you can pass in/out very large set of data inside a transaction by using temp tables. Temporary tables are one of the greatest features of SQL dbs. Here's one fact, it most often takes as long to transfer data from/to a query/function as it takes to execute it. By storing data on the server side, you shave that cost off. This is something most people don't think about when dealing with large sets of data.
2010/9/30 Andy Colson <andy@squeakycode.net>: > On 9/30/2010 8:52 AM, Vincenzo Romano wrote: >> >> 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. > > More of a question: why use temp tables at all? What does that offer that a > single, regular, session table does not? In my mind it can be a good and simple solution when I have more than 1 user needing a set of "personal" variables for each connection/session. Better advises are welcome, of course. -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS
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 >
2010/9/30 Pavel Stehule <pavel.stehule@gmail.com>: > Hello >>> 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 I do know I'll need to be careful, even without connection pooling. What'd be a different solution to implement session variables? Just PLPERL? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS
2010/9/30 Vincenzo Romano <vincenzo.romano@notorand.it>: > 2010/9/30 Pavel Stehule <pavel.stehule@gmail.com>: >> Hello >>>> 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 > > I do know I'll need to be careful, even without connection pooling. > What'd be a different solution to implement session variables? > Just PLPERL? plperl or C or custom guc .. the using plperl is probably most simple and fast Pavel http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables I don't know what do you do - but some times better is don't use a session variables on server - just use a application variables together with stored procedures. What I remember my last bigger project, we did a some problems with session variables from PHP, where PHP sessions sometimes recycled a db session, so probably better to don't use it. Pavel > > -- > Vincenzo Romano at NotOrAnd Information Technologies > Software Hardware Networking Training Support Security > -- > NON QVIETIS MARIBVS NAVTA PERITVS >
2010/9/30 Pavel Stehule <pavel.stehule@gmail.com>: > 2010/9/30 Vincenzo Romano <vincenzo.romano@notorand.it>: >> 2010/9/30 Pavel Stehule <pavel.stehule@gmail.com>: >>> Hello >>>>> 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 >> >> I do know I'll need to be careful, even without connection pooling. >> What'd be a different solution to implement session variables? >> Just PLPERL? > > plperl or C or custom guc .. the using plperl is probably most simple and fast > > Pavel > > http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables > > I don't know what do you do - but some times better is don't use a > session variables on server - just use a application variables > together with stored procedures. What I remember my last bigger > project, we did a some problems with session variables from PHP, where > PHP sessions sometimes recycled a db session, so probably better to > don't use it. > > Pavel A few of those session variables are needed by functions and views to change their output (and behavior). For example, one session variable is the "reference" time I use to select into history tables. A user can be using a reference time in the future (to see how projectons are), while another one is using the current_timestamp to work on current data. I don't see any better way than temp tables. So far. -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS
2010/9/30 Vincenzo Romano <vincenzo.romano@notorand.it>: > 2010/9/30 Pavel Stehule <pavel.stehule@gmail.com>: >> 2010/9/30 Vincenzo Romano <vincenzo.romano@notorand.it>: >>> 2010/9/30 Pavel Stehule <pavel.stehule@gmail.com>: >>>> Hello >>>>>> 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 >>> >>> I do know I'll need to be careful, even without connection pooling. >>> What'd be a different solution to implement session variables? >>> Just PLPERL? >> >> plperl or C or custom guc .. the using plperl is probably most simple and fast >> >> Pavel >> >> http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables >> >> I don't know what do you do - but some times better is don't use a >> session variables on server - just use a application variables >> together with stored procedures. What I remember my last bigger >> project, we did a some problems with session variables from PHP, where >> PHP sessions sometimes recycled a db session, so probably better to >> don't use it. >> >> Pavel > > A few of those session variables are needed by functions and views to > change their output (and behavior). > For example, one session variable is the "reference" time I use to > select into history tables. > A user can be using a reference time in the future (to see how > projectons are), while another one is using > the current_timestamp to work on current data. > I don't see any better way than temp tables. So far. > we did same design - it works well from single client application and not too much well from some application servers. Our solution was to use a special parameter for every function - like some session handle - and without session depending views we used a parametrized SRF functions with handle parameter. But this project was started about 2005, so now a situation can be different. Regards Pavel > -- > Vincenzo Romano at NotOrAnd Information Technologies > Software Hardware Networking Training Support Security > -- > NON QVIETIS MARIBVS NAVTA PERITVS >
--- On Thu, 9/30/10, Vincenzo Romano <vincenzo.romano@notorand.it> wrote:check if the temp_table alredy exist select 1 from pg_class where relname = 'prueba3' |
2010/9/30 Vincenzo Romano <vincenzo.romano@notorand.it>: > 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. Quoting from documentation (v9.0.0 at chapter 35.6, v8.4.4 at chapter 34.6) "A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions. A query using a volatile function will re-evaluate the function at every row where its value is needed." So, my question below stands still. > Is the planner caching the plan even in case of VOLATILE functions? I was expecting a volatile function not to be planned at definition time, but rather at execution time. Is this assumption wrong? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS