Behavior of ON DELETE CASCADE in CTEs

From: Kirk Parker <khp(at)equatoria(dot)us>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: Behavior of ON DELETE CASCADE in CTEs
Date: 2024-09-05 01:23:25
Message-ID: CANwZ8rm9Knqjf7K00j_mQW0Dkh45E+OQGtj+f4qESs4cE3YSSw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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?

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2024-09-05 01:27:41 Re: Behavior of ON DELETE CASCADE in CTEs
Previous Message Tom Lane 2024-09-04 20:45:54 Re: pg_upgrade -c cannot be run if old cluster is running