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

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