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

On Wed, Aug 17, 2022 at 10:11 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> 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 for the confirmation and for providing related detailed information.

--
With Regards,
Amit Kapila.