On Thu, Oct 1, 2020 at 8:02 PM David G. Johnston <david.g.johns...@gmail.com> 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 <jonathanrstr...@gmail.com> > 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. > > [1] > https://github.com/postgres/postgres/commit/2f178441044be430f6b4d626e4dae68a9a6f6cec > > > 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.
