Re: Behavior of ON DELETE CASCADE in CTEs

On Wednesday, September 4, 2024, Kirk Parker <khp(at)equatoria(dot)us> wrote:

>
> And the relevance to this current list is: if it IS intended behavior, can
> it be documented somewhere?
>

It’s follows from this paragraph:

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

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
<https://www.postgresql.org/docs/current/mvcc.html>), 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.

David J.