ERROR: out of shared memory
SQL state: 53200
Hint: You might need to increase max_locks_per_transaction.
Context: SQL statement "drop table if exists temp_rslt"
-------------------------------------------------------------------------
Here is an overview of the processing that is causing this.
I have a function I wrote named fGetQuestions. This function is very complex, but one of the things it does is create several TEMP tables. Here is a snipit of code that does it:
drop table if exists temp_rslt;
drop table if exists campuslocation_rslt;
drop table if exists final_rslt;
create temporary table temp_rslt of ypxportal2__fgetquestions on commit drop;
create temporary table campuslocation_rslt of ypxportal2__fgetquestions on commit drop;
create temporary table final_rslt of ypxportal2__fgetquestions on commit drop;
If I run only this function (fGetQuestions) from the PGADMIN3 edit screen, there is no problem.
So, now I wanted to stress test this function, so I wrote a testdriver function which calls fGetQuestions within a loop each time with a different set of parameter values. This driver is simple enough that posting the code is probably the best way to describe it, so here it is ...
CREATE OR REPLACE FUNCTION mikes_fget_questions_tester()
RETURNS void AS
$BODY$
declare
sql_select VARCHAR (16000);
c record;
begin
delete from mikes_test_results;
for c in (select * from ext_mikes_debug_log_vals where src = 'LCD1_LOG 1'
) loop -- get parameters
sql_select :=
'insert into mikes_test_results (SELECT '||c.vals_key::text||' as vals_key ,x.* FROM pxportal2__fgetquestions(
'''||c.web_site_name||''','||'
'''||c.portal_name||''','||'
'''||c.question_set_name||''','||'
--snip for brevity ---
'|| coalesce(''''||c.country_code||'''','null::character varying')||')x)'
;
execute sql_select;
end loop;
end$BODY$
After 1,231 iteration of the "for c in (select" loop,the ERROR: out of shared memory is thrown. In other words, after 1,231 calls to fGetQuestions.
Is there anything I can do to make sure that when fGetQuestions returns to mikes_fget_questions_tester() that all of the fGetQuestions resources are freed?
At a higher level, is there a better way to bulk/stress test any given function in general?
All comments and advice welcome.
Thanks
Mike