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.
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?