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?
