Re: Rows removed on child table when updating parent partitioned table.

On Thu, Oct 1, 2020 at 8:02 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)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 <jonathanrstrong(at)gmail(dot)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.