Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 11:06 AM Glen Huang <heyhgl(at)gmail(dot)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.