Thread: 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?
--------------------------------------------------------
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:
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.David J.
> The sub-statements in
WITH are executed concurrentlyThat 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), 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.
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), 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.