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
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_--