From kong_mansatiansin@hotmail.com Tue Jan  29 02:32:56 2013
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37072=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U010K-0006YF-JO
	for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jan 2013 02:32:56 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37072=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U010K-0003kd-2u
	for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jan 2013 02:32:56 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kong_mansatiansin@hotmail.com>) id 1U010I-0003jd-OG
	for pgsql-sql@postgresql.org; Tue, 29 Jan 2013 02:32:54 +0000
Received: from dub0-omc2-s11.dub0.hotmail.com ([157.55.1.150])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kong_mansatiansin@hotmail.com>) id 1U010H-0007dQ-7Q
	for pgsql-sql@postgresql.org; Tue, 29 Jan 2013 02:32:54 +0000
Received: from DUB116-W6 ([157.55.1.137]) by dub0-omc2-s11.dub0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.4675);
	Mon, 28 Jan 2013 18:32:52 -0800
X-EIP: [8v2TNuswp8dCtfBxm4Dek1PlZrT6fg59]
X-Originating-Email: [kong_mansatiansin@hotmail.com]
Message-ID: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_8023ae8b-619c-4252-aea9-718d1d03c10b_"
From: Kong Man <kong_mansatiansin@hotmail.com>
To: <pgsql-sql@postgresql.org>
Subject: Writeable CTE Not Working?
Date: Mon, 28 Jan 2013 18:32:51 -0800
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 29 Jan 2013 02:32:52.0018 (UTC)
	FILETIME=[EFAFE120:01CDFDC8]
X-Pg-Spam-Score: 0.3 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

--_8023ae8b-619c-4252-aea9-718d1d03c10b_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Can someone explain how this writable CTE works?  Or does it not?

What I tried to do was to make those non-null/non-empty values of suppliers=
.suppliercode unique by (1) nullifying any blank=2C but non-null=2C supplie=
rcode=2C then (2) appending the supplierid values to the suppliercode value=
s for those duplicates.  The writeable CTE=2C upd_code=2C did not appear to=
 work=2C allowing the final UPDATE statement to=2C unexpectedly=2C fill wha=
t used to be empty values with '-'||suppliercode.

WITH upd_code AS (
  UPDATE suppliers SET suppliercode =3D NULL=20
  WHERE suppliercode IS NOT NULL=20
  AND length(trim(suppliercode)) =3D 0
)
=2C ranked_on_code AS (
  SELECT supplierid
  =2C trim(suppliercode)||'-'||supplierid AS new_code
  =2C rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplier=
id)
  FROM suppliers
  WHERE suppliercode IS NOT NULL
  AND NOT inactive AND type !=3D 'car'
)
UPDATE suppliers
SET suppliercode =3D new_code
FROM ranked_on_code
WHERE suppliers.supplierid =3D ranked_on_code.supplierid
AND rank > 1=3B

I have seen similar behavior in the past and could not explain it.  Any exp=
lanation is much appreciated.
Thanks=2C
-Kong
 		 	   		  =

--_8023ae8b-619c-4252-aea9-718d1d03c10b_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Tahoma
}
--></style></head>
<body class=3D'hmmessage'><div dir=3D'ltr'>
Can someone explain how this writable CTE works?&nbsp=3B Or does it not?<br=
><br>What I tried to do was to make those non-null/non-empty values of supp=
liers.suppliercode unique by (1) nullifying any blank=2C but non-null=2C su=
ppliercode=2C then (2) appending the supplierid values to the suppliercode =
values for those duplicates.&nbsp=3B The writeable CTE=2C upd_code=2C did n=
ot appear to work=2C allowing the final UPDATE statement to=2C unexpectedly=
=2C fill what used to be empty values with '-'||suppliercode.<br><br>WITH u=
pd_code AS (<br>&nbsp=3B UPDATE suppliers SET suppliercode =3D NULL <br>&nb=
sp=3B WHERE suppliercode IS NOT NULL <br>&nbsp=3B AND length(trim(supplierc=
ode)) =3D 0<br>)<br>=2C ranked_on_code AS (<br>&nbsp=3B SELECT supplierid<b=
r>&nbsp=3B =2C trim(suppliercode)||'-'||supplierid AS new_code<br>&nbsp=3B =
=2C rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplierid=
)<br>&nbsp=3B FROM suppliers<br>&nbsp=3B WHERE suppliercode IS NOT NULL<br>=
&nbsp=3B AND NOT inactive AND type !=3D 'car'<br>)<br>UPDATE suppliers<br>S=
ET suppliercode =3D new_code<br>FROM ranked_on_code<br>WHERE suppliers.supp=
lierid =3D ranked_on_code.supplierid<br>AND rank &gt=3B 1=3B<br><br>I have =
seen similar behavior in the past and could not explain it.&nbsp=3B Any exp=
lanation is much appreciated.<br>Thanks=2C<br>-Kong<br> 		 	   		  </div></=
body>
</html>=

--_8023ae8b-619c-4252-aea9-718d1d03c10b_--




From vyegorov@gmail.com Tue Jan  29 07:40:26 2013
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37073=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U05nu-0001t6-6e
	for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jan 2013 07:40:26 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37073=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U05nt-0007y4-4x
	for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jan 2013 07:40:25 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <vyegorov@gmail.com>) id 1U05nr-0007xw-1e
	for pgsql-sql@postgresql.org; Tue, 29 Jan 2013 07:40:23 +0000
Received: from mail-oa0-f52.google.com ([209.85.219.52])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <vyegorov@gmail.com>) id 1U05no-0003fG-Vl
	for pgsql-sql@postgresql.org; Tue, 29 Jan 2013 07:40:22 +0000
Received: by mail-oa0-f52.google.com with SMTP id k14so131117oag.11
	for <pgsql-sql@postgresql.org>; Mon, 28 Jan 2013 23:40:20 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:x-received:in-reply-to:references:date:message-id
	:subject:from:to:cc:content-type:content-transfer-encoding;
	bh=pfuQ3576GuuviWHkDZX6qMaOKwgSxwBdZz6yaVM8a7Q=;
	b=Dv4FZWN83HqZa9HsJYETKkiyic3DjADuuaeUl32EHnOpBxThwywG/O85NfAtSuVUB7
	JikI9DGklMlU5zwxlwQgkwmzTH0+zp6/ytwDwhpdFvvgKllcb0lFYTLuz+sFaKQtl/BI
	+SY7QzAIX9xLcX60l7GzqD0gtcD3t1hnWwNuUWn1v26X/mMfF2FYx5QE/uLHhW5z1vxt
	fqHa0IV/NdmmtAWTwlVUDEF9E1295qwmx2QOrMKnBcpHDwpCNS/ifJ7clps/PcHhI5+A
	JzG4xtPElkUf/qXlMB8QBSkwTUBoZso7oZj7Qi7O1IwmWseFPe7QhKg18Uev9RKsWlMP
	tj8w==
MIME-Version: 1.0
X-Received: by 10.60.8.134 with SMTP id r6mr39842oea.53.1359445220298; Mon, 28
	Jan 2013 23:40:20 -0800 (PST)
Received: by 10.76.154.135 with HTTP; Mon, 28 Jan 2013 23:40:20 -0800 (PST)
In-Reply-To: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>
References: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>
Date: Tue, 29 Jan 2013 09:40:20 +0200
Message-ID: 
 <CAGnEbohaO2r8Zg=PXNsAOEG+c+8oUwO_2z7X6QKj+3QE=wtDbA@mail.gmail.com>
Subject: Re: Writeable CTE Not Working?
From: =?UTF-8?B?0JLQuNC60YLQvtGAINCV0LPQvtGA0L7Qsg==?= <vyegorov@gmail.com>
To: Kong Man <kong_mansatiansin@hotmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

2013/1/29 Kong Man <kong_mansatiansin@hotmail.com>:
> Can someone explain how this writable CTE works?  Or does it not?

They surely do, I use this feature a lot.
Take a look at the description in the docs:
http://www.postgresql.org/docs/current/interactive/queries-with.html#QUERIE=
S-WITH-MODIFYING


> WITH upd_code AS (
>   UPDATE suppliers SET suppliercode =3D NULL
>   WHERE suppliercode IS NOT NULL
>   AND length(trim(suppliercode)) =3D 0
> )
> , ranked_on_code AS (
>   SELECT supplierid
>   , trim(suppliercode)||'-'||supplierid AS new_code
>   , rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplier=
id)
>   FROM suppliers
>   WHERE suppliercode IS NOT NULL
>   AND NOT inactive AND type !=3D 'car'
> )
> UPDATE suppliers
> SET suppliercode =3D new_code
> FROM ranked_on_code
> WHERE suppliers.supplierid =3D ranked_on_code.supplierid
> AND rank > 1;

I see 2 problems with this query:
1) CTE is just a named subquery, in your query I see no reference to
the =E2=80=9Cupd_code=E2=80=9D CTE.
   Therefore it is never gets called;
2) In order to get data-modifying CTE to return anything, you should
use RETURNING clause,
    simplest form would be just RETURNING *

Hope this helps.

--=20
Victor Y. Yegorov


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



From kong_mansatiansin@hotmail.com Tue Jan  29 19:29:53 2013
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37074=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U0GsT-0007fk-0C
	for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jan 2013 19:29:53 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37074=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U0GsR-0005HC-V5
	for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jan 2013 19:29:52 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kong_mansatiansin@hotmail.com>) id 1U0GsR-0005GM-5E
	for pgsql-sql@postgresql.org; Tue, 29 Jan 2013 19:29:51 +0000
Received: from dub0-omc2-s20.dub0.hotmail.com ([157.55.1.159])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kong_mansatiansin@hotmail.com>) id 1U0GsL-0007pW-PM
	for pgsql-sql@postgresql.org; Tue, 29 Jan 2013 19:29:50 +0000
Received: from DUB116-W10 ([157.55.1.137]) by dub0-omc2-s20.dub0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.4675);
	Tue, 29 Jan 2013 11:29:40 -0800
X-EIP: [qaOG3XCA6LNQHVHI79M+C6B32EywZYaJ]
X-Originating-Email: [kong_mansatiansin@hotmail.com]
Message-ID: <DUB116-W10DDC6C15D2D368AD772C08B1F0@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_9ec64a61-b2ab-489d-980b-a570d21bb1d4_"
From: Kong Man <kong_mansatiansin@hotmail.com>
To: <vyegorov@gmail.com>
CC: <pgsql-sql@postgresql.org>
Subject: Re: Writeable CTE Not Working?
Date: Tue, 29 Jan 2013 11:29:40 -0800
Importance: Normal
In-Reply-To: 
 <CAGnEbohaO2r8Zg=PXNsAOEG+c+8oUwO_2z7X6QKj+3QE=wtDbA@mail.gmail.com>
References: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>,
	<CAGnEbohaO2r8Zg=PXNsAOEG+c+8oUwO_2z7X6QKj+3QE=wtDbA@mail.gmail.com>
MIME-Version: 1.0
X-OriginalArrivalTime: 29 Jan 2013 19:29:40.0851 (UTC)
	FILETIME=[FBC90430:01CDFE56]
X-Pg-Spam-Score: -1.0 (-)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

--_9ec64a61-b2ab-489d-980b-a570d21bb1d4_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


Hi Victor=2C

> I see 2 problems with this query:
> 1) CTE is just a named subquery=2C in your query I see no reference to
> the =93upd_code=94 CTE.
>    Therefore it is never gets called=3B

So=2C in conclusion=2C my misconception about CTE in general was that all C=
TE get called without being referenced.

Thank you much for the explanation. =20
-Kong

 		 	   		  =

--_9ec64a61-b2ab-489d-980b-a570d21bb1d4_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Tahoma
}
--></style></head>
<body class=3D'hmmessage'><div dir=3D'ltr'>
Hi Victor=2C<br><br>&gt=3B I see 2 problems with this query:<br>&gt=3B 1) C=
TE is just a named subquery=2C in your query I see no reference to<br>&gt=
=3B the =93upd_code=94 CTE.<br>&gt=3B    Therefore it is never gets called=
=3B<br><br>So=2C in conclusion=2C my misconception about CTE in general was=
 that all CTE get called without being referenced.<br><br>Thank you much fo=
r the explanation.&nbsp=3B <br>-Kong<br><br> 		 	   		  </div></body>
</html>=

--_9ec64a61-b2ab-489d-980b-a570d21bb1d4_--




From tgl@sss.pgh.pa.us Wed Jan  30 01:16:47 2013
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37075=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U0MIB-0004Yw-1u
	for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jan 2013 01:16:47 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37075=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U0MI9-0008FQ-Ua
	for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jan 2013 01:16:45 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1U0MI9-0008FL-4N
	for pgsql-sql@postgresql.org; Wed, 30 Jan 2013 01:16:45 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1U0MI7-00024Q-Sk
	for pgsql-sql@postgresql.org; Wed, 30 Jan 2013 01:16:44 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id r0U1GeNw018862;
	Tue, 29 Jan 2013 20:16:40 -0500 (EST)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kong Man <kong_mansatiansin@hotmail.com>
cc: vyegorov@gmail.com, pgsql-sql@postgresql.org
Subject: Re: Writeable CTE Not Working?
In-reply-to: <DUB116-W10DDC6C15D2D368AD772C08B1F0@phx.gbl>
References: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>,
	<CAGnEbohaO2r8Zg=PXNsAOEG+c+8oUwO_2z7X6QKj+3QE=wtDbA@mail.gmail.com>
	<DUB116-W10DDC6C15D2D368AD772C08B1F0@phx.gbl>
Comments: In-reply-to Kong Man <kong_mansatiansin@hotmail.com>
	message dated "Tue, 29 Jan 2013 11:29:40 -0800"
Date: Tue, 29 Jan 2013 20:16:40 -0500
Message-ID: <18861.1359508600@sss.pgh.pa.us>
X-Pg-Spam-Score: -2.4 (--)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Kong Man <kong_mansatiansin@hotmail.com> writes:
> Hi Victor,
>> I see 2 problems with this query:
>> 1) CTE is just a named subquery, in your query I see no reference to
>> the “upd_code” CTE.
>> Therefore it is never gets called;

> So, in conclusion, my misconception about CTE in general was that all CTE get called without being referenced.

I think this explanation is wrong --- if you run the query with EXPLAIN
ANALYZE, you can see from the rowcounts that the writable CTE *does* get
run to completion, as indeed is stated to be the behavior in the fine
manual.

However, for a case like this where the main query isn't reading from
the CTE, the CTE will get cycled to completion after the main query is
done.  I think what is happening is that the main query is updating all
the rows in the table, and then when the CTE comes along it thinks the
rows are already updated in the current command, so it doesn't replace
'em a second time.  This is a consequence of the fact that the same
command-counter ID is used throughout the query.  My recollection is
that that choice was intentional and that doing it differently would
break use-cases that are less outlandish than this one.  I don't recall
specific examples though.

Why are you trying to update the same table in two different parts of
this query, anyway?  The best you can really hope for with that is
unspecified behavior --- we will surely not promise that one of them
completes before the other starts, so in general there's no way to be
sure which one would process a particular row first.

			regards, tom lane


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



From kong_mansatiansin@hotmail.com Wed Jan  30 02:46:08 2013
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37076=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U0Nge-0001r6-CJ
	for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jan 2013 02:46:08 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37076=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U0Ngd-0000or-AP
	for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jan 2013 02:46:07 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kong_mansatiansin@hotmail.com>) id 1U0Ngc-0000ol-0a
	for pgsql-sql@postgresql.org; Wed, 30 Jan 2013 02:46:06 +0000
Received: from dub0-omc2-s1.dub0.hotmail.com ([157.55.1.140])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kong_mansatiansin@hotmail.com>) id 1U0NgY-0003N0-7h
	for pgsql-sql@postgresql.org; Wed, 30 Jan 2013 02:46:03 +0000
Received: from DUB116-W35 ([157.55.1.136]) by dub0-omc2-s1.dub0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.4675);
	Tue, 29 Jan 2013 18:45:56 -0800
X-EIP: [6AAV6PKGj4WRWrgfwjC1T1BytjTL7g8k]
X-Originating-Email: [kong_mansatiansin@hotmail.com]
Message-ID: <DUB116-W353526CD12D2106765BAE08B1E0@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_dbc9a56e-0678-41b3-8210-2732fd87f90b_"
From: Kong Man <kong_mansatiansin@hotmail.com>
To: <tgl@sss.pgh.pa.us>
CC: <vyegorov@gmail.com>, <pgsql-sql@postgresql.org>
Subject: Re: Writeable CTE Not Working?
Date: Tue, 29 Jan 2013 18:45:56 -0800
Importance: Normal
In-Reply-To: <18861.1359508600@sss.pgh.pa.us>
References: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>,
	<CAGnEbohaO2r8Zg=PXNsAOEG+c+8oUwO_2z7X6QKj+3QE=wtDbA@mail.gmail.com>
	<DUB116-W10DDC6C15D2D368AD772C08B1F0@phx.gbl>,
	<18861.1359508600@sss.pgh.pa.us>
MIME-Version: 1.0
X-OriginalArrivalTime: 30 Jan 2013 02:45:56.0702 (UTC)
	FILETIME=[EDCEE7E0:01CDFE93]
X-Pg-Spam-Score: -2.4 (--)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

--_dbc9a56e-0678-41b3-8210-2732fd87f90b_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


> I think this explanation is wrong --- if you run the query with EXPLAIN
> ANALYZE=2C you can see from the rowcounts that the writable CTE *does* ge=
t
> run to completion=2C as indeed is stated to be the behavior in the fine
> manual.
>=20
> However=2C for a case like this where the main query isn't reading from
> the CTE=2C the CTE will get cycled to completion after the main query is
> done.  I think what is happening is that the main query is updating all
> the rows in the table=2C and then when the CTE comes along it thinks the
> rows are already updated in the current command=2C so it doesn't replace
> 'em a second time.  This is a consequence of the fact that the same
> command-counter ID is used throughout the query.  My recollection is
> that that choice was intentional and that doing it differently would
> break use-cases that are less outlandish than this one.  I don't recall
> specific examples though.

Cool.  Now I understand it much better. =20

> Why are you trying to update the same table in two different parts of
> this query=2C anyway?  The best you can really hope for with that is
> unspecified behavior --- we will surely not promise that one of them
> completes before the other starts=2C so in general there's no way to be
> sure which one would process a particular row first.

It was just my misuse of writable CTE thinking it would be more efficient t=
han separate statements.

Best regards=2C
-Kong
 		 	   		  =

--_dbc9a56e-0678-41b3-8210-2732fd87f90b_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Tahoma
}
--></style></head>
<body class=3D'hmmessage'><div dir=3D'ltr'>
&gt=3B I think this explanation is wrong --- if you run the query with EXPL=
AIN<br>&gt=3B ANALYZE=2C you can see from the rowcounts that the writable C=
TE *does* get<br>&gt=3B run to completion=2C as indeed is stated to be the =
behavior in the fine<br>&gt=3B manual.<br>&gt=3B <br>&gt=3B However=2C for =
a case like this where the main query isn't reading from<br>&gt=3B the CTE=
=2C the CTE will get cycled to completion after the main query is<br>&gt=3B=
 done.  I think what is happening is that the main query is updating all<br=
>&gt=3B the rows in the table=2C and then when the CTE comes along it think=
s the<br>&gt=3B rows are already updated in the current command=2C so it do=
esn't replace<br>&gt=3B 'em a second time.  This is a consequence of the fa=
ct that the same<br>&gt=3B command-counter ID is used throughout the query.=
  My recollection is<br>&gt=3B that that choice was intentional and that do=
ing it differently would<br>&gt=3B break use-cases that are less outlandish=
 than this one.  I don't recall<br>&gt=3B specific examples though.<br><br>=
Cool.&nbsp=3B Now I understand it much better.&nbsp=3B <br><br>&gt=3B Why a=
re you trying to update the same table in two different parts of<br>&gt=3B =
this query=2C anyway?  The best you can really hope for with that is<br>&gt=
=3B unspecified behavior --- we will surely not promise that one of them<br=
>&gt=3B completes before the other starts=2C so in general there's no way t=
o be<br>&gt=3B sure which one would process a particular row first.<br><br>=
It was just my misuse of writable CTE thinking it would be more efficient t=
han separate statements.<br><br><div>Best regards=2C<br>-Kong<br></div> 		 =
	   		  </div></body>
</html>=

--_dbc9a56e-0678-41b3-8210-2732fd87f90b_--