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

On Thursday, August 4, 2022 8:19 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> 
> On Thu, Aug 4, 2022 at 3:00 PM houzj.fnst@fujitsu.com
> <houzj.fnst@fujitsu.com> wrote:
> >
> > On Thursday, August 4, 2022 4:53 AM Brad Nicholson
> <brad.nicholson@instacart.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.

Thanks. Attach the patch with a new testcase.

Best regards,
Hou zj