pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWriteable CTE Not Working?
5+ messages / 3 participants
[nested] [flat]
* Writeable CTE Not Working?
@ 2013-01-29 02:32 Kong Man <kong_mansatiansin@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kong Man @ 2013-01-29 02:32 UTC (permalink / raw)
To: pgsql-sql
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, but non-null, suppliercode, then (2) appending the supplierid values to the suppliercode values for those duplicates. The writeable CTE, upd_code, did not appear to work, allowing the final UPDATE statement to, unexpectedly, fill what used to be empty values with '-'||suppliercode.
WITH upd_code AS (
UPDATE suppliers SET suppliercode = NULL
WHERE suppliercode IS NOT NULL
AND length(trim(suppliercode)) = 0
)
, ranked_on_code AS (
SELECT supplierid
, trim(suppliercode)||'-'||supplierid AS new_code
, rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplierid)
FROM suppliers
WHERE suppliercode IS NOT NULL
AND NOT inactive AND type != 'car'
)
UPDATE suppliers
SET suppliercode = new_code
FROM ranked_on_code
WHERE suppliers.supplierid = ranked_on_code.supplierid
AND rank > 1;
I have seen similar behavior in the past and could not explain it. Any explanation is much appreciated.
Thanks,
-Kong
=
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Writeable CTE Not Working?
@ 2013-01-29 07:40 Виктор Егоров <vyegorov@gmail.com>
parent: Kong Man <kong_mansatiansin@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Виктор Егоров @ 2013-01-29 07:40 UTC (permalink / raw)
To: Kong Man <kong_mansatiansin@hotmail.com>; +Cc: pgsql-sql
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#QUERIES-WITH-MODIFYING
> WITH upd_code AS (
> UPDATE suppliers SET suppliercode = NULL
> WHERE suppliercode IS NOT NULL
> AND length(trim(suppliercode)) = 0
> )
> , ranked_on_code AS (
> SELECT supplierid
> , trim(suppliercode)||'-'||supplierid AS new_code
> , rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplierid)
> FROM suppliers
> WHERE suppliercode IS NOT NULL
> AND NOT inactive AND type != 'car'
> )
> UPDATE suppliers
> SET suppliercode = new_code
> FROM ranked_on_code
> WHERE suppliers.supplierid = 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 “upd_code” 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.
--
Victor Y. Yegorov
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Writeable CTE Not Working?
@ 2013-01-29 19:29 Kong Man <kong_mansatiansin@hotmail.com>
parent: Виктор Егоров <vyegorov@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kong Man @ 2013-01-29 19:29 UTC (permalink / raw)
To: vyegorov@gmail.com; +Cc: pgsql-sql
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.
Thank you much for the explanation.
-Kong
=
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Writeable CTE Not Working?
@ 2013-01-30 01:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kong Man <kong_mansatiansin@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2013-01-30 01:16 UTC (permalink / raw)
To: Kong Man <kong_mansatiansin@hotmail.com>; +Cc: vyegorov@gmail.com; pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Writeable CTE Not Working?
@ 2013-01-30 02:45 Kong Man <kong_mansatiansin@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Kong Man @ 2013-01-30 02:45 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: vyegorov@gmail.com; pgsql-sql
> 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.
Cool. Now I understand it much better.
> 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.
It was just my misuse of writable CTE thinking it would be more efficient than separate statements.
Best regards,
-Kong
=
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-01-30 02:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-29 02:32 Writeable CTE Not Working? Kong Man <kong_mansatiansin@hotmail.com>
2013-01-29 07:40 ` Виктор Егоров <vyegorov@gmail.com>
2013-01-29 19:29 ` Kong Man <kong_mansatiansin@hotmail.com>
2013-01-30 01:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-01-30 02:45 ` Kong Man <kong_mansatiansin@hotmail.com>
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