On Thu, Oct 1, 2020 at 8:02 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
The convention on these lists is to inline or bottom-post, please do not top-post.On Thu, Oct 1, 2020 at 10:41 AM Jonathan Strong <jonathanrstrong@xxxxxxxxx> wrote:I've been away from coding for several years, but dusting off my chops and getting back up to speed with PostgreSQL (love it!). So please forgive me if my early answers here come off as naive. But my understanding of this suggests that you shouldn't be using "update" on a serial field.Yes Jonathan, your present understanding is flawed. The OP has provided a self-contained simple test case for the problem at hand - which even if not "best practice" is indeed valid to do and demonstrates the problem quite clearly. Without actually testing it out I would say that this is likely indeed an oversight in the partition row movement feature - it didn't take into account the ON UPDATE/ON DELETE clause.Adding Robert Hass who committed the row movement feature [1].We document on the UPDATE reference page that such an update is performed as a DELETE + INSERT. Given that implementation detail, the observed behavior is what one would expect if no special consideration has been given to make row movement between partitions preserve (via deferred evaluation), or recreate the foreign key relationship.For now I would say you should consider the two features incompatible; and we need to update the documentation to reflect that reality more directly, barring a solution being proposed, and hopefully back-patched, instead. I concur with the observation that one would expect these two features to interact better with each other and think it could possibly be done as a bug fix for the POLA violation.David J.
Regardless of how postgres implement the updates:
Don’t think it’s a bug that executing an update, you are ending up with fewer rows than you initially had?
Is the perfect silent row-killer
Even worse, the deleted rows are from another table without realizing it.
If no one else gives an opinion I will open a bug for at least, force an update of the documentation.
