Re: Logical Replication of sequences

On Mon, 29 Sept 2025 at 09:59, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Fri, Sep 26, 2025 at 12:55 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > On Thu, 25 Sept 2025 at 12:23, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > >
> > > sequencesync_list_invalidate_cb():
> > > 5)
> > >
> > > + /* invalidate all entries */
> > > + hash_seq_init(&status, sequences_to_copy);
> > > + while ((entry = (LogicalRepSequenceInfo *) hash_seq_search(&status)) != NULL)
> > > + entry->entry_valid = false;
> > >
> > > Can you please elaborate when this case can be hit? I see such logic
> > > in all such invalidation functions registered with
> > > CacheRegisterRelcacheCallback(), but could not find any relevant
> > > comment.
> >
> > I noticed this could happen in cases like:
> > create publication for all tables;
> > alter publication on many relations;
> >
> > but there might be more apart from this
> >
>
> Okay. I will review more here.
>
> > Rest of the comments were addressed.
> > The attached patch has the changes for the same.
> >
>
> Thanks.
>
> I found a race condition between the apply worker and the sequence
> sync worker, where a sequence might be deleted from
> pg_subscription_rel and fail to be re-added when it should be.
>
> Steps:
>
> 1)
> The publisher and subscriber both have two sequences: seq1 and seq2.
>
> 2)
> A REFRESH PUBLICATION SEQUENCES command is executed on the subscriber.
> Before the sequencesync worker on the subscriber can locate the
> corresponding sequence on the publisher, the sequence gets dropped on
> the publisher. In other words, the sequence is removed from the
> publisher before walrcv_exec() is called in copy_sequences().
>
> 3)
> Before the sequencesync worker on the subscriber can drop the sequence
> locally, it is recreated on the publisher. Then, a second REFRESH
> PUBLICATION SEQUENCES command is executed on the subscriber. (i.e.,
> before RemoveSubscriptionRel() is reached in copy_sequences(), the
> sequence is already recreated on the publisher and a new refresh
> command is issued on the subscriber.)
>
> 4)
> During this second REFRESH PUBLICATION SEQUENCES, the sequence is
> found to already exist in pg_subscription_rel, so it is not re-added.
> However, concurrently, the sequencesync worker from the first refresh
> proceeds and drops the sequence from the subscriber.
>
> As a result, the sequence ends up being removed from
> pg_subscription_rel, even though it should have remained after both
> REFRESH PUBLICATION SEQUENCES commands.

I've resolved it by modifying the sequence sync worker to no longer
remove sequences from pg_subscription_rel, aligning its behavior with
that of the tablesync worker. This change ensures consistency and also
addresses the reported problem. The attached patch includes the
necessary modifications.

Regards,
Vignesh