Re: Updating PK and all FKs to it in CTE

On Thu, Jul 29, 2021 at 10:51:09AM -0400, Tom Lane wrote:
>I tried to reproduce your problem and failed; the attached script
>acts as I'd expect. So there must be some moving part you've not
>mentioned. Can yo create a similar self-contained example that
>fails?

Thanks very much for trying this. I failed to create a self-contained
example and just now realized what the problem is: a trigger function
accidentally created an entry with the old ID (changing the primary key
was not anticipated until now), throwing off the constraint check. I
noticed when running this explicitly in a transaction and then
inspecting the table that triggered the error and also got the same
error with ON UPDATE CASCADE. My apologies, it just took a while for me
to realize where this entry came from and that it was not the CTE or ON
UPDATE CASCADE causing this. It's all resolved now and I am glad the CTE
behaves as expected.

Kind regards,
Tom