Re: sequencesync worker race with REFRESH SEQUENCES

On Wed, Jul 15, 2026 at 2:44 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Wed, 15 Jul 2026 at 07:01, Hayato Kuroda (Fujitsu)
> <kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
> >
>
> > 02.
> > ```
> > @@ -2142,6 +2145,12 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
> > * retain_dead_tuples.
> > */
> > check_pub_rdt = sub->retaindeadtuples;
> > +
> > + /*
> > + * If the subscription already has sequences, ensure the new
> > + * publisher is new enough to synchronize them.
> > + */
> > + check_pub_seq = HasSubscriptionSequences(subid);
> > ```
> >
> > HasSubscriptionSequences() can spend long time because it searches pg_subscription_rel
> > catalog linerly. Do you think we can do the deferrable approach? I imagined to
> > check the remote version first and then check the catalog if the version is prior
> > than PG 19. Or it might be more costly to check the remote version?
>
> If we do it that way, every ALTER SUBSCRIPTION CONNECTION or SERVER
> would connect to the publisher, even for subscriptions that don't have
> any sequences. At present, we only establish a connection to the
> publisher when it is actually required, such as when update_failover,
> update_two_phase, or check_pub_rdt is true. Establishing a connection
> to the publisher is not a lightweight operation. It involves creating
> a new connection, performing authentication, and incurring network
> round trips before we can determine whether the publication contains
> any sequences. In contrast, HasSubscriptionSequences() only performs a
> local catalog lookup on the subscriber to determine whether the
> subscription has any sequences, which is significantly cheaper.
>

I agree with this reasoning for not performing connection before
checking sequences. I think if we want to make
HasSubscriptionSequences() cheap then we can have an index on srsubid
in pg_subscription_rel or somehow track the presence of sequences at
pg_subscription level. The other idea to optimize is that for the
cases where we anyway need to form connection with the publisher, we
can defer checking the sequences. However, I feel changing the
connection string shouldn't be performed often enough to worry about
or adding additional complexity in code or adding more optimizations
for it.

--
With Regards,
Amit Kapila.