Re: Rows removed on child table when updating parent partitioned table. - Mailing list pgsql-general

From David G. Johnston
Subject Re: Rows removed on child table when updating parent partitioned table.
Date
Msg-id CAKFQuwZSeG1ceBdngfyLmiT-rqdCcrg_-1M8vC8fTjwVmZwwrQ@mail.gmail.com
Whole thread Raw
In response to Re: Rows removed on child table when updating parent partitioned table.  (Jonathan Strong <jonathanrstrong@gmail.com>)
Responses Re: Rows removed on child table when updating parent partitioned table.  (Jonathan Strong <jonathanrstrong@gmail.com>)
Re: Rows removed on child table when updating parent partitioned table.  (Eduard Català <eduard.catala@gmail.com>)
List pgsql-general
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@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.



pgsql-general by date:

Previous
From: Jonathan Strong
Date:
Subject: Re: Rows removed on child table when updating parent partitioned table.
Next
From: Jonathan Strong
Date:
Subject: Re: Rows removed on child table when updating parent partitioned table.