On Thu, Apr 1, 2021 at 11:06 AM Glen Huang <heyhgl@xxxxxxxxx> 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.
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.
