Re: Logical Replication of sequences

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.

thanks
Shveta