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
