On Thu, Apr 1, 2021 at 11:06 AM Glen Huang <hey...@gmail.com> wrote: > Care to expand why they are tricker? I presume they run the risk of being > referenced more than once? >
There are lots of gotchas. It's also been a few years since I dug deep into this, so some of this may have changed in more recent versions. * Changes in a CTE aren't visible to later CTEs since they haven't happened yet. Often times people are updating a table and then doing further things and can hit situations they weren't expecting. db=> create table foo ( a integer primary key ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE db=> insert into foo values ( 1 ); INSERT 0 1 db=> with upt as ( update foo set a = 2 ) insert into foo values (1); ERROR: duplicate key value violates unique constraint "foo_pkey" DETAIL: Key (a)=(1) already exists. * Unless you reference between the CTEs to force ordering, CTEs can happen in any order, which can cause things to get out of the order people expected. * Just like you can cause deadlocks between threads in a transaction, you can do the same thing by shoving all those statements into a single CTE query.
