Update isn't working using with clause

Question

WITH first_insert AS 
(
    INSERT INTO first_insert_table (column_of_first_insert_table) 
    VALUES (10) 
    RETURNING first_insert_table_id
),
second_insert AS 
(
    INSERT INTO second_insert_table(column_of_second_insert_table)
    VALUES((SELECT * FROM first_insert)) 
    RETURNING second_insert_table_id
)  
--here doesn't update column_of_first_insert_table!
UPDATE first_insert_table 
SET column_of_first_insert_table = column_of_first_insert_table + 1
WHERE first_insert_table_id = (SELECT * FROM first_insert) ;

Why doesn't this query update column_of_first_insert_table column of first_insert_table, when I try to update first_insert_table table?

Answer 1

The reason why it doesn't perform the expected update is for the same reason why this simplified version of your example will not show the row being inserted in the WITH clause:

with cte as (
  insert into first_insert_table (column_of_first_insert_table)
  values (10)
)
select * from first_insert_table

When executing the above statement, the INSERT does happen successfully, but the SELECT portion of the statement does not see it, because the SELECT only sees the data that existed at the moment the statement began executing.

The same is happening in your example. The WHERE clause of your UPDATE can only find rows that existed at the moment the whole statement began, so it doesn't see the row that was inserted in the WITH clause.

This behavior is documented (7.8.2. Data-Modifying Statements in WITH):

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.