Re: No-op updates with partitioning and logical replication started failing in version 13

On Thu, Aug 4, 2022 at 3:00 PM houzj(dot)fnst(at)fujitsu(dot)com
<houzj(dot)fnst(at)fujitsu(dot)com> wrote:
>
> On Thursday, August 4, 2022 4:53 AM Brad Nicholson <brad(dot)nicholson(at)instacart(dot)com> wrote:
> > We've hit an interesting change with table partitioning and logical replication that was introduced in Postgres 13.
> > I've tested this on PG 14.4, 13.7, 12.11, 11.16 and 10.18. These are brew installed binaries on OSX, but we've also
> > seen this on other platforms.
> >
> > The error happens under the following conditions:
> > - parent table does not have a primary key
> > - parent table is part of a logical replication publication
> > - a no-op update that does not include the partition key is run against the parent.
> >
> > In Postgres versions < 13, the update succeeds with UPDATE 0.
> >
> > In Postgres versions >= 13, it fails with:
> >
> > ERROR: cannot update table "t1" because it does not have a replica identity and publishes updates
> > HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
> >
> > Here is a self contained test case:
> >
> > create table t1 (id int, created_at timestamp, dat varchar) partition by range (created_at);
> > create table t1_child partition of t1 for values from ( '2022-01-01 00:00:00') to ('2022-01-31 00:00:00');
> > insert into t1 (id, dat,created_at) values (1, 'test', '2022-01-02 00:00:00');
> > create publication test_pub for all tables;
> > update t1 set dat = 'foo1' where id = 1 and 1=0;
> >
> > Worth mentioning, the 1=0 is a Rails thing.
> >
> > I would expect to see an UPDATE 0 in the newer versions instead of the failure.
>
> Hi,
>
> From the error message, it seems we checked the pub action and replica
> identity on the partitioned table ('t1'), but it looks uncommon because we
> should only check the replica identity on the leaf partition which we actually
> perform DML on.
>

I agree with your analysis and fix. Adding Amit L., the author of this
feature to check his views on this issue.

--
With Regards,
Amit Kapila.