Create a temp table (if not exists) for use into a custom procedure

Question

I'm trying to get the hang of using temp tables:

CREATE OR REPLACE FUNCTION test1(user_id BIGINT) RETURNS BIGINT AS
$BODY$

BEGIN
  create temp table temp_table1
  ON COMMIT DELETE ROWS

  as SELECT table1.column1, table1.column2 
  FROM table1
  INNER JOIN -- ............

  if exists (select * from temp_table1) then
    -- work with the result
    return 777;
  else 
    return 0;
  end if;

END;
$BODY$
LANGUAGE plpgsql;

I want the row temp_table1 to be deleted immediately or as soon as possible, that's why I added ON COMMIT DELETE ROWS. Obviously, I got the error:

ERROR:  relation "temp_table1" already exists

I tried to add IF NOT EXISTS but I couldn't, I simply couldn't find working example of it that would be the I'm looking for.

Your suggestions?

score 22 · Accepted Answer · answered Apr 08 '14 at 08:46

22

DROP Table each time before creating TEMP table as below:

BEGIN
  DROP TABLE IF EXISTS temp_table1;
  create temp table temp_table1
  -- Your rest Code comes here

answered Apr 08 '14 at 08:46

Ilesh Patel

2,053
16
27

1

are you positive it's more effective than DELETE ROWS? – Incerteza Apr 08 '14 at 09:16
1

Yes because only delete rows will not drop Table and that's way you can not create this table again. – Ilesh Patel Apr 08 '14 at 09:56
but I don't need to. I'm asking about in terms of performance also. – Incerteza Apr 08 '14 at 10:07
I was under the impression that this is bad practice as you would have to keep both tables in memory (alternatively spill to disk if the size requires it), is this correct? Also; even if temp_table1 did not exists at the point of DROP it would still cause bloat due to locks right? – Calle Bergström May 02 '17 at 08:40

score 7 · Answer 2 · answered Apr 08 '19 at 19:13

The problem of temp tables is that dropping and recreating temp table bloats pg_attribute heavily and therefore one sunny morning you will find db performance dead, and pg_attribute 200+ gb while your db would be like 10gb.

So we're very heavy on temp tables having >500 rps and async i\o via nodejs and thus experienced a very heavy bloating of pg_attribute because of that. All you are left with is a very aggressive vacuuming which halts performance. All answers given here do not solve this, because they all bloat pg_attribute heavily.

So the solution is elegantly this

create temp table if not exists my_temp_table (description) on commit delete rows;

So you go on playing with temp tables and save your pg_attribute.

score 3 · Answer 3 · answered Apr 08 '14 at 09:29

3

You want to DROP term table after commit (not DELETE ROWS), so:

begin
  create temp table temp_table1
  on commit drop
...

Documentation

answered Apr 08 '14 at 09:29

klin

112,967
15
204
232

are you positive it's more effective than DELETE ROWS? – Incerteza Apr 08 '14 at 09:32
1

I'm sure it's more effective than 'on commit delete rows' + 'drop table if exists'. – klin Apr 08 '14 at 09:54
but I don't really need to drop it as long as I can delete rows. Why would I re-create it each time instead of just deleting its rows? – Incerteza Apr 08 '14 at 10:08
1

Unfortunately construction `create temp table if not exists ... as ...` is not allowed in postgres (http://www.postgresql.org/docs/9.2/static/sql-createtableas.html). As a matter of efficiency it's surely not a crucial problem. – klin Apr 08 '14 at 10:44

Create a temp table (if not exists) for use into a custom procedure

3 Answers3