From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kong Man <kong_mansatiansin@hotmail.com>
Cc: vyegorov@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Writeable CTE Not Working?
Date: Tue, 29 Jan 2013 20:16:40 -0500
Message-ID: <18861.1359508600@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kong_mansatiansin@hotmail.com, vyegorov@gmail.com
Subject: Re: Writeable CTE Not Working?
In-Reply-To: <18861.1359508600@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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