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?

Answer 1

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.

Answer 2

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