Behavior of ON DELETE CASCADE in CTEs
It appears that in With-queries (CTE) the ON CASCADE DELETE deletions
happen at the very end of the entire statement. I have two questions about
this:
(1) Is this a correct observation?
(2) Is this intentional behavior, or only an accident of the implementation?
I can't find anything in the docs covering this aspect. It's useful
behavior but of course I don't want to continue using it, if it could
change.
--------------------------------------------------------
Let me try a simplified example derived from our actual code.
"cust_roa_detail" references "order_line_items", with ON DELETE CASCADE
specified. I have verified that this is all defined correctly--deleting a
single row in order_line_items does in fact remove the referencing rows.
"cust_roa_detail" also references "cust_charge". When a row in the former
goes away, we want to remove its effects from the balance columns in the
latter. This is what the "charge_restore" section does.
with line_delete as
(
delete from order_line_items where line_id = :lineid returning line_id,
amount
),
pend_restore as
(
select cust_charge_id, sum(amount) as amt, sum(discount) as disc,
sum(adjustment) as adj from cust_roa_detail where line_id = (select line_id
from line_del) group by 1
),
charge_restore as
(
update cust_charge set paid = paid - (select amt from pend_restore),
discount = discount - (select disc from pend_restore),
adjustment = adjustment - (select adj from pend_restore)
where id = (select cust_charge_id from pend_restore)
returning id, paid, discount, adjustment
), ....
select * from line_delete;
The above came from rearranging a previous version of the with-query. It
seems to work fine, but on reviewing it I was surprised to notice that the
row(s) that are cascade-deleted by "line_delete" still are found by the
subsequent "pend_restore" query. Whereas if you issue these two queries
separately, of course the 2nd one retrieves nothing.
It seems sensible that the ON CASCADE DELETE actions would take place at
the very end of the compound statement; but it's quite another to rely on
that behavior if it's merely an artifact of the current implementation.
And the relevance to this current list is: if it IS intended behavior, can
it be documented somewhere?
Kirk Parker <khp@equatoria.us> writes:
It appears that in With-queries (CTE) the ON CASCADE DELETE deletions
happen at the very end of the entire statement. I have two questions about
this:
(1) Is this a correct observation?
(2) Is this intentional behavior, or only an accident of the implementation?
I believe it's required by SQL spec. Or more accurately, the spec
says that constraint-triggered actions happen either at the end of the
statement or the end of the transaction, depending on whether you set
them as "deferred".
regards, tom lane
On Wednesday, September 4, 2024, Kirk Parker <khp@equatoria.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.
The sub-statements in WITH are executed concurrently
That much I did get from the docs. Given each sub-statement is qualified
by the RETURNING results of the previous one, that should at least
guarantee the completeness of each query regardless of the order in which
individual rows are affected.
Thanks, this has been helpful -- as I reread the page you pointed to, this
jumped out at me:
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.
That I think specifically covers my question, and Tom Lane's note about
end-of-statement was also helpful.