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

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.

Here is a tiny patch to fix this.

Best regards,
Hou zj