pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: out of shared memory
12+ messages / 2 participants
[nested] [flat]
* ERROR: out of shared memory
@ 2016-11-02 21:31 Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Michael Moore @ 2016-11-02 21:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
@ 2016-11-02 21:49 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2016-11-02 21:49 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Wed, Nov 2, 2016 at 2:31 PM, Michael Moore <michaeljmoore@gmail.com>
wrote:
> 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;
>>
>>
>
[...]
>
>
> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
@ 2016-11-02 21:52 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Michael Moore @ 2016-11-02 21:52 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
I'll give that a go.
On Wed, Nov 2, 2016 at 2:49 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wed, Nov 2, 2016 at 2:31 PM, Michael Moore <michaeljmoore@gmail.com>
> wrote:
>
>> 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;
>>>
>>>
>>
>
> [...]
>
>
>>
>>
>> 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.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
@ 2016-11-02 22:03 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Michael Moore @ 2016-11-02 22:03 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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..
https://www.postgresql.org/docs/9.2/static/sql-createtableas.html
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:
>
>> On Wed, Nov 2, 2016 at 2:31 PM, Michael Moore <michaeljmoore@gmail.com>
>> wrote:
>>
>>> 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;
>>>>
>>>>
>>>
>>
>> [...]
>>
>>
>>>
>>>
>>> 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.
>>
>>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
@ 2016-11-02 22:28 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:29 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2016-11-02 22:28 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Wed, Nov 2, 2016 at 3:03 PM, Michael Moore <michaeljmoore@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..
>
> https://www.postgresql.org/docs/9.2/static/sql-createtableas.html
>
> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
@ 2016-11-02 22:29 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:33 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:33 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: David G. Johnston @ 2016-11-02 22:29 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Wed, Nov 2, 2016 at 3:28 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wed, Nov 2, 2016 at 3:03 PM, Michael Moore <michaeljmoore@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..
>>
>> https://www.postgresql.org/docs/9.2/static/sql-createtableas.html
>>
>> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:29 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
@ 2016-11-02 22:33 ` Michael Moore <michaeljmoore@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Michael Moore @ 2016-11-02 22:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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:
> On Wed, Nov 2, 2016 at 3:28 PM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Wed, Nov 2, 2016 at 3:03 PM, Michael Moore <michaeljmoore@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..
>>>
>>> https://www.postgresql.org/docs/9.2/static/sql-createtableas.html
>>>
>>> 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.
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:29 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
@ 2016-11-02 22:33 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:55 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2016-11-02 22:33 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Wed, Nov 2, 2016 at 3:29 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wed, Nov 2, 2016 at 3:28 PM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Wed, Nov 2, 2016 at 3:03 PM, Michael Moore <michaeljmoore@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..
>>>
>>> https://www.postgresql.org/docs/9.2/static/sql-createtableas.html
>>>
>>> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:29 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:33 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
@ 2016-11-02 22:55 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-02 23:06 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Michael Moore @ 2016-11-02 22:55 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
David,
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:
>
>> On Wed, Nov 2, 2016 at 3:28 PM, David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>
>>> On Wed, Nov 2, 2016 at 3:03 PM, Michael Moore <michaeljmoore@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..
>>>>
>>>> https://www.postgresql.org/docs/9.2/static/sql-createtableas.html
>>>>
>>>> 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.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:29 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:33 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:55 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
@ 2016-11-02 23:06 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 23:08 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2016-11-02 23:06 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Wed, Nov 2, 2016 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com>
wrote:
> David,
> 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?
>
>
You missed the part where you immediately TRUNCATE the table after
conditionally creating it...
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:29 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:33 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:55 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 23:06 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
@ 2016-11-02 23:08 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 23:31 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2016-11-02 23:08 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Wed, Nov 2, 2016 at 4:06 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wed, Nov 2, 2016 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com>
> wrote:
>
>> David,
>> 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?
>>
>>
> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: out of shared memory
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:29 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:33 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:55 ` Re: ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 23:06 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 23:08 ` Re: ERROR: out of shared memory David G. Johnston <david.g.johnston@gmail.com>
@ 2016-11-02 23:31 ` Michael Moore <michaeljmoore@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Michael Moore @ 2016-11-02 23:31 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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:
> On Wed, Nov 2, 2016 at 4:06 PM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Wed, Nov 2, 2016 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com>
>> wrote:
>>
>>> David,
>>> 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?
>>>
>>>
>> 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.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2016-11-02 23:31 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-02 21:31 ERROR: out of shared memory Michael Moore <michaeljmoore@gmail.com>
2016-11-02 21:49 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 21:52 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:03 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:28 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:29 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:33 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-02 22:33 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 22:55 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-02 23:06 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 23:08 ` David G. Johnston <david.g.johnston@gmail.com>
2016-11-02 23:31 ` Michael Moore <michaeljmoore@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox