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?

Inoffensive

Answer 1

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

Politicize

are you positive it's more effective than DELETE ROWS? – Inoffensive

Yes because only delete rows will not drop Table and that's way you can not create this table again. – Politicize

but I don't need to. I'm asking about in terms of performance also. – Inoffensive

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? – Dysphasia

Answer 2

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.

Nez

Answer 3

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

begin
  create temp table temp_table1
  on commit drop
...

Documentation

Lipoprotein

are you positive it's more effective than DELETE ROWS? – Inoffensive

I'm sure it's more effective than 'on commit delete rows' + 'drop table if exists'. – Lipoprotein

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? – Inoffensive

Unfortunately construction create temp table if not exists ... as ... is not allowed in postgres (postgresql.org/docs/9.2/static/sql-createtableas.html). As a matter of efficiency it's surely not a crucial problem. – Lipoprotein