Thread: [9.0] On temporary tables

[9.0] On temporary tables

From
Vincenzo Romano
Date:
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

Re: [9.0] On temporary tables

From
Tom Lane
Date:
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

Re: [9.0] On temporary tables

From
Vincenzo Romano
Date:
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

Re: [9.0] On temporary tables

From
Pavel Stehule
Date:
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
>

Re: [9.0] On temporary tables

From
Vincenzo Romano
Date:
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

Re: [9.0] On temporary tables

From
Andy Colson
Date:
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

Re: [9.0] On temporary tables

From
Grzegorz Jaśkiewicz
Date:
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.

Re: [9.0] On temporary tables

From
Vincenzo Romano
Date:
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

Re: [9.0] On temporary tables

From
Pavel Stehule
Date:
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
>

Re: [9.0] On temporary tables

From
Vincenzo Romano
Date:
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

Re: [9.0] On temporary tables

From
Pavel Stehule
Date:
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
>

Re: [9.0] On temporary tables

From
Vincenzo Romano
Date:
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

Re: [9.0] On temporary tables

From
Pavel Stehule
Date:
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
>

Re: [9.0] On temporary tables

From
Lennin Caro
Date:
--- On Thu, 9/30/10, Vincenzo Romano <vincenzo.romano@notorand.it> wrote:

From: Vincenzo Romano <vincenzo.romano@notorand.it>
Subject: [GENERAL] [9.0] On temporary tables
To: "PostgreSQL General" <pgsql-general@postgresql.org>
Date: Thursday, September 30, 2010, 11:09 AM

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

check if the temp_table alredy exist
select 1 from pg_class where relname = 'prueba3'


Re: [9.0] On temporary tables

From
Vincenzo Romano
Date:
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