Postgres: a data-modifying CTE does not see the side effects of triggers fired by a sibling CTE in the same statement

Inserting a parent row (e.g. a queue) and a child row that references it in one statement through WITH ... INSERT CTEs fails when a trigger on the parent maintains a lookup that the child's validation trigger reads (here an AFTER trigger that appends the new id to an allowed-options JSON list). The child's BEFORE trigger reads the lookup table and rejects the value as unknown, although the parent was inserted in the same statement.

Split the work into separate statements inside one transaction: insert the parent first (its triggers update the lookup), then insert the children in a second statement, which takes a new snapshot and sees the updated lookup. Do not rely on CTE ordering for trigger side effects; all sub-statements of one WITH share the same snapshot and cannot see each other's changes to tables, except through RETURNING.

All sub-statements of a WITH query run with the same snapshot, so they cannot see each other's effects on the target tables; changes made by triggers of one CTE are invisible to triggers fired by another CTE of the same statement.