Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c24fO-0000yb-Va for pgsql-sql@arkaria.postgresql.org; Wed, 02 Nov 2016 23:09:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1c24fO-0004SG-IG for pgsql-sql@arkaria.postgresql.org; Wed, 02 Nov 2016 23:09:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c24eR-0003KP-Jr for pgsql-sql@postgresql.org; Wed, 02 Nov 2016 23:08:59 +0000 Received: from mail-oi0-x22b.google.com ([2607:f8b0:4003:c06::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1c24eO-00022C-Vo for pgsql-sql@postgresql.org; Wed, 02 Nov 2016 23:08:59 +0000 Received: by mail-oi0-x22b.google.com with SMTP id 128so45076392oih.0 for ; Wed, 02 Nov 2016 16:08:56 -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=5gb77D+8Ga2lhlr5I689BVvQLo6/ngPC/l6897iIGe8=; b=l15L4b1KX0D34SHRhjoUReXI3/JE96RENn8spmDdnDt6uMXA9TuUF9PFrNgaFVz2f7 whhMRAxEZhkDf0031i6YUxO4pHiZZ1bQaAD2NyUo7PoaAoT+N9vNxkeWnxJle6Xktv19 oMp8aPZBb023WGRm7Ydc8iH5bBZGh9XVK9ayl0bnBpqES4NjQXxNidezsKeT7dhfJE6k 5YLF365rZChSWp2DCu1b5qrmN3m/+7voA8ZJmAHW7usV0xQnVGSlu9lw6h/8Rso1odH9 J6sjsv6VMuaenvwJFuoQUzi/Y1HOLHwHN+JDMAivTwQQzibfbZjykjk9Riv9iwr9WR8b VfEw== 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=5gb77D+8Ga2lhlr5I689BVvQLo6/ngPC/l6897iIGe8=; b=f/f2UW6wroR4SZkX+4/7uvw3gzCovLozdQIgTadjecI+5nR6FD6sZBbJJxcoSMSQ7R Y/bR2Xl9ID0J6jxFWE4ULIVsoElta7BTiyAPzKF+DTEHk8odiAg0vbcfgULvISea9moY 1q7oQ7ScfRk+aBAP6oxlf9ZzBBPsjeLKvAPr1RDsjae2WPJBC62fNAHDGfTGPV4QnqRG vHaQ3wyq7tI4+Ry0CCQmN7VaGvxP18YDiwBdTVmfL1UVSvedpkFGLV7BTHYkLB6PPsBC wHdc/14hYRcMqtj7DMAPCduJJCkOEFi484fXpKTGpNEw5yhDl6//AtfaLS7jCx6s9+J6 f1JQ== X-Gm-Message-State: ABUngvdY2xC020vbRhW2aWC4vUpF9TbLOD7bSl1OXcnllhCqlHeqyR7DyaZMLrxV1USQlmbyD7yPxO7elfocEA== X-Received: by 10.202.105.198 with SMTP id e189mr5412057oic.91.1478128135357; Wed, 02 Nov 2016 16:08:55 -0700 (PDT) MIME-Version: 1.0 Received: by 10.157.43.137 with HTTP; Wed, 2 Nov 2016 16:08:54 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Wed, 2 Nov 2016 16:08:54 -0700 Message-ID: Subject: Re: ERROR: out of shared memory To: Michael Moore Cc: postgres list Content-Type: multipart/alternative; boundary=001a1141b26670cc3d05405986cf X-Pg-Spam-Score: -2.7 (--) 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 --001a1141b26670cc3d05405986cf Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 aft= er > 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 Method 2 will not.=E2=80=8B David J. --001a1141b26670cc3d05405986cf Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Wed, No= v 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,=C2=A0
I think that: ...

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

is not functionally equivalent to=C2=A0<= /div>

Method 2
drop table if exists temp_rslt;
create temporary table =C2=A0temp_rslt= of ypxportal2__fgetquestions =C2=A0on commit drop;

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 Metho= d 1 or Method 2 at this step)=C2=A0
step 3. =C2=A0 =C2=A0 B inser= ts one record into=C2=A0temp_rsl= t
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= =A0temp_rslt=C2=A0wil= l never be more than 1.
If, at step 2, we use Method = 2, then =C2=A0count(*)=C2=A0temp= _rslt=C2=A0will increment with each iteration of steps 1 thru 5.=

Make sense?


<= div style=3D"font-family:arial,helvetica,sans-serif;display:inline">=E2=80= =8BYou missed the part where you immediately TRUNCATE the table after condi= tionally 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 Method 2 will not.=E2=80=8B

David J.

--001a1141b26670cc3d05405986cf--