UNION ALL vs UNION for Update/Returning + Select?

Question

I'm trying to turn a query idempotent by marking rows as updated. However, part of the query spec is to return the IDs of rows that matched the filter. I was thinking of doing something like the following:

WITH
prev as (
     SELECT id
       FROM books
      WHERE id = any($1::uuid[])
        AND updated
),
updated as (
     UPDATE books
        SET author = $2 || author, updated = true
      WHERE id = any($1::uuid[])
        AND not updated
  RETURNING id
)
SELECT id FROM prev
UNION ALL
SELECT id FROM updated

I'm hoping to avoid de de-dupe step from using UNION instead of UNION ALL so was wondering if the semantics of the operator guarantee that the 1st query does not see the results of the 2nd.

Related Qs:

Using CTEs for update + select:
Execution order for functions with side-effects: Does postgres union guarantee order of execution when invoking functions with side effects?
Manual de-duping: Update a table from a union select statement

score 1 · Accepted Answer · answered Dec 07 '19 at 13:57

The PostgreSQL WITH docs specify that the two CTEs will be executed concurrently and in the same snapshot, so the UNION ALL is safe to use.

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.

UNION ALL vs UNION for Update/Returning + Select?

1 Answers1