[SOLVED] Only one of two UPDATEs in CTEs is executed?

Only one of two UPDATEs in CTEs is executed?

I have this situation where I have to use CTE's in this form

WITH
update_field_a AS (
update super_table SET
  a = ....
WHERE condition to update a hold true
RETURNING TRUE
),
update_field_b AS (
update super_table SET
  b = ....
WHERE condition to update b hold true
RETURNING TRUE
)
SELECT 
  (SELECT true FROM update_field_a) as updated_a,
  (SELECT true FROM update_field_b) as updated_b,

So basically two CTE's updating different fields of the same table with each having it's own update condition in the WHERE clause.

The problem is that the behaviour I am seeing is that only of of the CTE runs depending on which of the SELECT i call first in the last SELECT query.

That is given this

SELECT 
  (SELECT true FROM update_field_a) as updated_a, <-- first select
  (SELECT true FROM update_field_b) as updated_b,

The update_field_a CTE will run and update the table while update_field_b does not run because (SELECT true FROM update_field_a) as updated_a is called first.

But if I have:

SELECT 
  (SELECT true FROM update_field_b) as updated_b, <-- first select
  (SELECT true FROM update_field_a) as updated_a,

The update_field_b CTE will run and update the table while update_field_a does not run because (SELECT true FROM update_field_a) as updated_a is called first.

How can I ensure both CTE's run? I thought referencing them in the SELECT statement will trigger both of them, but for some strange reason it is only the first that get reference is the one that runs.

Note this query is part of a Rust application using the sqlx library. Update: I see the same behaviour if the query is executed directly in psql terminal, so the Rust/Sqlx details has no impact.

Solution

All CTEs and the main query see the same snapshot of underlying tables. Updating the same row multiple times does not work.

How can I ensure both CTE's run?

You cannot. Either split into two separate queries, or combine both updates into one. Like:

UPDATE super_table
SET    a = CASE WHEN condition_a THEN ... ELSE a END
     , b = CASE WHEN condition_b THEN ... ELSE b END
WHERE (condition_a OR condition_b)
RETURNING condition_a AS updated_a
        , condition_b AS updated_b;