From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kirk Parker <khp@equatoria.us>
cc: pgsql-docs@lists.postgresql.org
Subject: Re: Behavior of ON DELETE CASCADE in CTEs
In-reply-to: 
 <CANwZ8rm9Knqjf7K00j_mQW0Dkh45E+OQGtj+f4qESs4cE3YSSw@mail.gmail.com>
References: 
 <CANwZ8rm9Knqjf7K00j_mQW0Dkh45E+OQGtj+f4qESs4cE3YSSw@mail.gmail.com>
Comments: In-reply-to Kirk Parker <khp@equatoria.us>
	message dated "Wed, 04 Sep 2024 18:23:25 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1729419.1725499661.1@sss.pgh.pa.us>
Date: Wed, 04 Sep 2024 21:27:41 -0400
Message-ID: <1729420.1725499661@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1729420.1725499661%40sss.pgh.pa.us>
Precedence: bulk

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