PostgreSQL – How to Avoid Creating and Dropping Temp Tables in Functions

functionspostgresqltemporary-tables

I have been creating some functions lately that run into the "out of shared memory" warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs.

As far as i understand from the answer in this post ("PostgreSQL complaining about shared memory, but shared memory seems to be OK") creating and dropping temp tables creates locks which can result in the earlier-mentioned warning.

for example:

create or replace function remove_vertices(par_geom geometry) 
returns geometry as $$ 
DECLARE 
cnt int;
BEGIN

drop table if exists invalid_pnts;
create temp table invalid_pnts as 
select * from selfintersects(par_geom) ; 
-- calls other function. selfintersects(par_geom) returns X rows with 4 columns/attributes, 
-- where X corresponds to number of faulty vertices in a postgis geometry
-- can return 0 rows   

select count(*) into cnt from invalid_pnts;
if cnt > 0 
then -- do something with invalid_pnts table and the input "par_geom"
    
else 
    return par_geom 

end $$
language 'plpgsql';

Since this function is supposed to be called per row of a table (i.e. select remove_vertices(geom) from some_table), the dropping and creation of the temp table can occur as many times as there are rows in a table.

What is an alternative to dropping/creating temp tables, if you need a "table variable" in a function?

Best Answer

Wouldn't a simple loop be enough?

create or replace function remove_vertices(par_geom geometry) 
returns geometry as $$ 
DECLARE 
  declare invalid_pnts record;
begin
  for invalid_pnts in select * from selfintersects(par_geom)
  loop
    -- do something with every record
  end loop;

  if not found then
    -- loop was empty
    return par_geom;
  end if;
end;
$$ language plpgsql;

Frequent creation of temporary tables will also bloat the system catalog and therefore slow down each and every query. It is really a bad idea to create and drop temporary tables (say a few tens per second) frequently in PostgreSQL.

Related Question