Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c250S-0001lc-Oj for pgsql-sql@arkaria.postgresql.org; Wed, 02 Nov 2016 23:31:44 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1c250R-0004lP-VI for pgsql-sql@arkaria.postgresql.org; Wed, 02 Nov 2016 23:31:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c250O-0004i7-9s for pgsql-sql@postgresql.org; Wed, 02 Nov 2016 23:31:40 +0000 Received: from mail-ua0-x232.google.com ([2607:f8b0:400c:c08::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1c250L-0008Uh-KL for pgsql-sql@postgresql.org; Wed, 02 Nov 2016 23:31:38 +0000 Received: by mail-ua0-x232.google.com with SMTP id 20so26044696uak.0 for ; Wed, 02 Nov 2016 16:31:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=gjiCHCNdpcTXyEfaV3BZWTSBOXxpDm7wBGZYO5qa3zQ=; b=BvP6DNSgDpypi9tDIyJhaUgtM+Hgc3gHveT6MMMgN9/r5SJFbHXfCcTSG0cN6e2YbM cT12/jRwt1ScJU31rBCENdXqbT8quRMoQ0mF7hVA9uihuEb7g3CQ7g6E90lb7jg15LGC xwrh+CUhJ1PtKyHn0MLOzclHygwoCS1fkqYLCZMQv+I0VCns+Vtjw1okGzd1YhnQeoRI gowRCr48jpf97kU2dNB4St6zByFBubLlK49DiWbHcYqZt7wbDUR+DkNGnUbWtFxfHwNS t14G+7Ahkt1WEIm3nE4HwZMH17XyBxl5ps0HzDyp2pFwH9e1JT+WvoyCzBWnv6PfxrmE 3DYg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=gjiCHCNdpcTXyEfaV3BZWTSBOXxpDm7wBGZYO5qa3zQ=; b=HQtoUHwXK/37BmIVOlo89XxVn9es0v8hT6eOu1SwXmABwWhNdUtt+lTPWuogGnE6JW y/3UHuXdfdx3Ff66qWzjdTLabs4Ty6TjkksqmtrqIFrWsonigz3FcfsuCm4T0JJEhoz9 Mwnazd/1NWC3zyDz4kX8oGjZYAsIiWG1VaZIkNisG/m4G3qAq0pMDFrrOOs5UL9L0xix cgK+W4ZeA2logMAPK3geD2HBNAxUdFhiQR0OzDHAhxzJyBIwQjlX7CSgJy4CNnSdsW16 pw98h4PpHXYP86ALUFJCMFkPqEH05bgY6dyarC2fkFLyCesB/TGnvUyH5fEMWzxwqFFg y9mw== X-Gm-Message-State: ABUngvcSLicq4CRuKbgzyG9Z4V+zIu+z00dJ8yC2jlmCutC8tzziJCoZeJ49JefEMVqgHLjqaE/jiL390ebllw== X-Received: by 10.176.0.180 with SMTP id 49mr4967137uaj.32.1478129496196; Wed, 02 Nov 2016 16:31:36 -0700 (PDT) MIME-Version: 1.0 Received: by 10.31.98.3 with HTTP; Wed, 2 Nov 2016 16:31:35 -0700 (PDT) In-Reply-To: References: From: Michael Moore Date: Wed, 2 Nov 2016 16:31:35 -0700 Message-ID: Subject: Re: ERROR: out of shared memory To: "David G. Johnston" Cc: postgres list Content-Type: multipart/alternative; boundary=001a113dc4168d8b53054059d7e2 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a113dc4168d8b53054059d7e2 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 >> 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? >>> >>> >> =E2=80=8BYou missed the part where you immediately TRUNCATE the table af= ter >> conditionally creating it... >> >> > =E2=80=8BAnd no, Method 2 will reset since you are continually dropping i= t. > > Method 1, without Truncate, will do the incrementing while Method 2 will > not.=E2=80=8B > > David J. > > --001a113dc4168d8b53054059d7e2 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Yes, I reversed my Methods in my summation.=C2=A0 If I und= erstand you correctly you are suggesting that I try:

create temporary table if not exists =C2=A0temp_rslt of ypxportal2_= _fgetquestions =C2=A0on commit drop;
truncate=C2=A0=C2=A0temp_rslt ;

=C2=A0I tried it and it works !!!

no more= =C2=A0ERROR: out of shared memory =C2=A0= !!!

Thanks so much!
Mike


=

On Wed, Nov 2, 20= 16 at 4:08 PM, David G. Johnston <david.g.johnston@gmail.com&= gt; wrote:
On Wed, Nov 2, 2= 016 at 4:06 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Nov 2, 201= 6 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
David,=C2=A0
I think that: ...

Method 1
create temporary table if not exists = =C2=A0temp_rslt of ypxportal2__fgetquestions =C2=A0on commit drop;

is not functionally equivalent to=C2=A0

Method 2
drop table if exists temp_rslt;
create temporary table =C2=A0temp_rslt of= ypxportal2__fgetquestions =C2=A0on commit drop;
<= br>
For example:

step 1. A calls B
=
step 2. =C2=A0 =C2=A0 B creates=C2=A0temp_rslt=C2=A0table =C2=A0 =C2=A0(substitute Method 1= or Method 2 at this step)=C2=A0
step 3. =C2=A0 =C2=A0 B inserts = one record into=C2=A0temp_rslt
step 4. =C2=A0 =C2=A0 B returns, without error, to A --= note, no commit is done
step 5. =C2=A0 =C2=A0 repeat #1

If, at step 2, we use Method 1, then =C2=A0count(*)=C2=A0<= b>temp_rslt=C2=A0will ne= ver be more than 1.
If, at step 2, we use Method 2, t= hen =C2=A0count(*)=C2=A0temp_rsl= t=C2=A0will increment with each iteration of steps 1 thru 5.
=

Make sense?


=E2=80=8BYou missed the part where you immed= iately TRUNCATE the table after conditionally creating it...


=E2=80=8BAnd no, Method 2 will reset since you are continually= dropping it.

Method 1, without Truncate, will do the incrementing while Meth= od 2 will not.=E2=80=8B

=
David J.


--001a113dc4168d8b53054059d7e2--