[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Updating PK and all FKs to it in CTE
From:       Tom Kazimiers <tom () voodoo-arts ! net>
Date:       2021-07-29 15:12:48
Message-ID: YQLFcA/olznkDF/4 () dewberry ! localdomain
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]