Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

[Rob Sargent]

I think the table is dropped by end of function

> On Jan 28, 2018, at 2:45 PM, David G. Johnston  
> wrote:
> 
>> On Sunday, January 28, 2018, Rob Sargent  wrote:
>> The commit in question is the function btw.
> 
> That doesn't make sense - functions can't commit.
> 
> David J.

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

[David G. Johnston]

On Sunday, January 28, 2018, Rob Sargent  wrote:
>
> The commit in question is the function btw.
>

That doesn't make sense - functions can't commit.

David J.

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

[Rob Sargent]

 “On commit drop” is generally good practice for several reasons: if your 
function is called in a loop, or the create statement is in a loop or you want 
to call your function more than once in a given session (with out explicitly 
dropping your temp table).
The commit in question is the function btw. 

> On Jan 28, 2018, at 8:53 AM, Andy Colson  wrote:
> 
>> On 01/28/2018 08:46 AM, Ryan Murphy wrote:
>>I believe the main, and maybe only, concern is the bloating of the system 
>> catalog tables since you are constantly adding and removing records.  Yes, 
>> they will be vacuumed but vacuuming and bloat on catalog tables slows every 
>> single query down to some, degree since every query has to lookup its 
>> objects is those catalogs.  Though caching probably alleviates some of that
>> Yes, that's exactly the concern I heard, thanks for reminding me.
>> If I want to e.g. temporarily store a "setof records" or a "table" result in 
>> a variable as part of a calculation in a plpgsql function, do I have any 
>> other option than CREATE TEMPORARY TABLE?  It didn't seem to work when I 
>> DECLAREd a variable of type "setof table_name" or "setof 
>> table_name%rowtype", and then SELECT INTO it.
> 
> You may not need temp tables at all.  You can use subselects, derived tables, 
> and cte's:
> 
> select sum(a+b) as total
> from (
>   select a, b+1
>   from detail
> ) as tmpx;
> 
> 
> This does the same thing as a temp table, with no temp table.
> 
> -Andy
> 

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

[Andy Colson]

On 01/28/2018 08:46 AM, Ryan Murphy wrote:

I believe the main, and maybe only, concern is the bloating of the system 
catalog tables since you are constantly adding and removing records.  Yes, they 
will be vacuumed but vacuuming and bloat on catalog tables slows every single 
query down to some, degree since every query has to lookup its objects is those 
catalogs.  Though caching probably alleviates some of that


Yes, that's exactly the concern I heard, thanks for reminding me.

If I want to e.g. temporarily store a "setof records" or a "table" result in a variable as part of 
a calculation in a plpgsql function, do I have any other option than CREATE TEMPORARY TABLE?  It didn't seem to work 
when I DECLAREd a variable of type "setof table_name" or "setof table_name%rowtype", and then 
SELECT INTO it.



You may not need temp tables at all.  You can use subselects, derived tables, 
and cte's:

select sum(a+b) as total
from (
   select a, b+1
   from detail
) as tmpx;


This does the same thing as a temp table, with no temp table.

-Andy

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

[David G. Johnston]

On Sunday, January 28, 2018, Ryan Murphy  wrote:

> Hello,
>
> I heard at a PostgreSQL talk that you should not liberally create temp
> tables in the course of frequently-used functions etc, because (roughly)
> you're using up some of the same resources that you for your regular tables.
>
> Is this true?  Is there an important reason not to have e.g. a plpgsql
> function that uses a temp table?  What are the specific problems if I do
> this?  Is the problem ameliorated if I add ON COMMIT DROP?
>

I believe the main, and maybe only, concern is the bloating of the system
catalog tables since you are constantly adding and removing records.  Yes,
they will be vacuumed but vacuuming and bloat on catalog tables slows every
single query down to some, degree since every query has to lookup its
objects is those catalogs.  Though caching probably alleviates some of that.

The way most temp tables are used on commit drop likely has little impact
on this, but the specific usage pattern matters a great deal in answering
the question.

David J.

Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

[Ryan Murphy]

Hello,

I heard at a PostgreSQL talk that you should not liberally create temp
tables in the course of frequently-used functions etc, because (roughly)
you're using up some of the same resources that you for your regular tables.

Is this true?  Is there an important reason not to have e.g. a plpgsql
function that uses a temp table?  What are the specific problems if I do
this?  Is the problem ameliorated if I add ON COMMIT DROP?

Best,
Ryan