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

On Thu, Aug 4, 2022 at 9:19 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> 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:
> > > 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;
> >
> > 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.

Sorry for the delay in replying. The fix sounds fine to me.

I would have thought that CheckValidResultRel() and thus
CheckCmdReplicaIdentity() would only ever get called on leaf partition
result relations (present in ModifyTable.resultRelations) and never on
any partitioned tables, but that is apparently not true in this case.
In this case, all leaf partitions would be pruned by the planner given
the WHERE clause, and in that special case, the planner puts the
partitioned table's RT index into ModifyTable.resultRelations, which
is otherwise never added there (see commit ab5fcf2b04f).

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com