Обсуждение: ERROR: out of shared memory
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:
If I run only this function (fGetQuestions) from the PGADMIN3 edit screen, there is no problem.
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;
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
ERROR: out of shared memorySQL state: 53200Hint: 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;
[...]
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?
Maybe try losing the DROP TABLES and just do:
CREATE TEMP TABLE ... IF NOT EXISTS ... ON COMMIT DROP;
TRUNCATE ...;
David J.
I'll give that a go.
On Wed, Nov 2, 2016 at 2:49 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
ERROR: out of shared memorySQL state: 53200Hint: 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;[...]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? Maybe try losing the DROP TABLES and just do:CREATE TEMP TABLE ... IF NOT EXISTS ... ON COMMIT DROP;TRUNCATE ...;David J.
That appears to not be valid syntax.
create temporary table temp_rslt of ypxportal2__fgetquestions if not exists on commit drop;
ERROR: syntax error at or near "if"
LINE 146: ...rary table temp_rslt of ypxportal2__fgetquestions if not exi..
Am I doing something wrong?
On Wed, Nov 2, 2016 at 2:52 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
I'll give that a go.On Wed, Nov 2, 2016 at 2:49 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:ERROR: out of shared memorySQL state: 53200Hint: 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;[...]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? Maybe try losing the DROP TABLES and just do:CREATE TEMP TABLE ... IF NOT EXISTS ... ON COMMIT DROP;TRUNCATE ...;David J.
That appears to not be valid syntax.create temporary table temp_rslt of ypxportal2__fgetquestions if not exists on commit drop;ERROR: syntax error at or near "if"LINE 146: ...rary table temp_rslt of ypxportal2__fgetquestions if not exi..Am I doing something wrong?
I always get the placement of IF NOT EXISTS confused - but you found the doc page with the syntax definition, do you have a question regarding what the correct order of parts should be?
David J.
That appears to not be valid syntax.create temporary table temp_rslt of ypxportal2__fgetquestions if not exists on commit drop;ERROR: syntax error at or near "if"LINE 146: ...rary table temp_rslt of ypxportal2__fgetquestions if not exi..Am I doing something wrong?I always get the placement of IF NOT EXISTS confused - but you found the doc page with the syntax definition, do you have a question regarding what the correct order of parts should be?
Actually, you didn't find the correct page...
This is a "CREATE TABLE" not "CREATE TABLE AS" command...
David J.
create temporary table if not exists temp_rslt of ypxportal2__fgetquestions on commit drop;
got it, thanks
On Wed, Nov 2, 2016 at 3:29 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
That appears to not be valid syntax.create temporary table temp_rslt of ypxportal2__fgetquestions if not exists on commit drop;ERROR: syntax error at or near "if"LINE 146: ...rary table temp_rslt of ypxportal2__fgetquestions if not exi..Am I doing something wrong?I always get the placement of IF NOT EXISTS confused - but you found the doc page with the syntax definition, do you have a question regarding what the correct order of parts should be?Actually, you didn't find the correct page...This is a "CREATE TABLE" not "CREATE TABLE AS" command...David J.
On Wed, Nov 2, 2016 at 3:29 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
That appears to not be valid syntax.create temporary table temp_rslt of ypxportal2__fgetquestions if not exists on commit drop;ERROR: syntax error at or near "if"LINE 146: ...rary table temp_rslt of ypxportal2__fgetquestions if not exi..Am I doing something wrong?I always get the placement of IF NOT EXISTS confused - but you found the doc page with the syntax definition, do you have a question regarding what the correct order of parts should be?Actually, you didn't find the correct page...This is a "CREATE TABLE" not "CREATE TABLE AS" command...
And the CREATE TABLE AS command does have IF NOT EXISTS - just not back in 9.2 which is what you linked to...
David J.
David,
I think that: ...
I think that: ...
Method 1
create temporary table if not exists temp_rslt of ypxportal2__fgetquestions on commit drop;
is not functionally equivalent to
Method 2
drop table if exists temp_rslt;
create temporary table temp_rslt of ypxportal2__fgetquestions on commit drop;
For example:
step 1. A calls B
step 2. B creates temp_rslt table (substitute Method 1 or Method 2 at this step)
step 3. B inserts one record into temp_rslt
step 4. B returns, without error, to A -- note, no commit is done
step 5. repeat #1
If, at step 2, we use Method 1, then count(*) temp_rslt will never be more than 1.
If, at step 2, we use Method 2, then count(*) temp_rslt will increment with each iteration of steps 1 thru 5.
Make sense?
On Wed, Nov 2, 2016 at 3:33 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Nov 2, 2016 at 3:29 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:That appears to not be valid syntax.create temporary table temp_rslt of ypxportal2__fgetquestions if not exists on commit drop;ERROR: syntax error at or near "if"LINE 146: ...rary table temp_rslt of ypxportal2__fgetquestions if not exi..Am I doing something wrong?I always get the placement of IF NOT EXISTS confused - but you found the doc page with the syntax definition, do you have a question regarding what the correct order of parts should be?Actually, you didn't find the correct page...This is a "CREATE TABLE" not "CREATE TABLE AS" command...And the CREATE TABLE AS command does have IF NOT EXISTS - just not back in 9.2 which is what you linked to...David J.
David,
I think that: ...Method 1create temporary table if not exists temp_rslt of ypxportal2__fgetquestions on commit drop;is not functionally equivalent toMethod 2drop table if exists temp_rslt;create temporary table temp_rslt of ypxportal2__fgetquestions on commit drop;For example:step 1. A calls Bstep 2. B creates temp_rslt table (substitute Method 1 or Method 2 at this step)step 3. B inserts one record into temp_rsltstep 4. B returns, without error, to A -- note, no commit is donestep 5. repeat #1If, at step 2, we use Method 1, then count(*) temp_rslt will never be more than 1.If, at step 2, we use Method 2, then count(*) temp_rslt will increment with each iteration of steps 1 thru 5.Make sense?
You missed the part where you immediately TRUNCATE the table after conditionally creating it...
David J.
David,
I think that: ...Method 1create temporary table if not exists temp_rslt of ypxportal2__fgetquestions on commit drop;is not functionally equivalent toMethod 2drop table if exists temp_rslt;create temporary table temp_rslt of ypxportal2__fgetquestions on commit drop;For example:step 1. A calls Bstep 2. B creates temp_rslt table (substitute Method 1 or Method 2 at this step)step 3. B inserts one record into temp_rsltstep 4. B returns, without error, to A -- note, no commit is donestep 5. repeat #1If, at step 2, we use Method 1, then count(*) temp_rslt will never be more than 1.If, at step 2, we use Method 2, then count(*) temp_rslt will increment with each iteration of steps 1 thru 5.Make sense?You missed the part where you immediately TRUNCATE the table after conditionally creating it...
And no, Method 2 will reset since you are continually dropping it.
Method 1, without Truncate, will do the incrementing while Method 2 will not.
David J.
Yes, I reversed my Methods in my summation. If I understand you correctly you are suggesting that I try:
create temporary table if not exists temp_rslt of ypxportal2__fgetquestions on commit drop;
truncate temp_rslt ;
I tried it and it works !!!
no more ERROR: out of shared memory !!!
Thanks so much!
Mike
On Wed, Nov 2, 2016 at 4:08 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
David,
I think that: ...Method 1create temporary table if not exists temp_rslt of ypxportal2__fgetquestions on commit drop;is not functionally equivalent toMethod 2drop table if exists temp_rslt;create temporary table temp_rslt of ypxportal2__fgetquestions on commit drop;For example:step 1. A calls Bstep 2. B creates temp_rslt table (substitute Method 1 or Method 2 at this step)step 3. B inserts one record into temp_rsltstep 4. B returns, without error, to A -- note, no commit is donestep 5. repeat #1If, at step 2, we use Method 1, then count(*) temp_rslt will never be more than 1.If, at step 2, we use Method 2, then count(*) temp_rslt will increment with each iteration of steps 1 thru 5.Make sense?You missed the part where you immediately TRUNCATE the table after conditionally creating it...And no, Method 2 will reset since you are continually dropping it.Method 1, without Truncate, will do the incrementing while Method 2 will not.David J.