Re: sequencesync worker race with REFRESH SEQUENCES - Mailing list pgsql-hackers

On Wed, Jul 22, 2026 at 8:16 AM vignesh C <vignesh21@gmail.com> wrote:
>
> On Tue, 21 Jul 2026 at 12:49, shveta malik <shveta.malik@gmail.com> wrote:
> >
> > The logic of fix looks good. A few comments:
> >
> > 1)
> > Note
> > Sequence synchronization requires the publisher to be running
> > PostgreSQL 19 or later.
> >
> > This note appears to be at odd position in
> > logical-replication-sequences.html. Please have a look at html and
> > move it to end of that section if you agree.
>
> Modified
>
> > 2)
> > +     from the tables themselves.  Note that
> > +     <link linkend="sql-altersubscription-params-refresh-sequences">
> > +     <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link> only
> > +     re-synchronizes sequences that are already known to the subscription
> > +     (see <xref linkend="logical-replication-sequences"/>); in particular, it
> > +     requires the publisher to be running <productname>PostgreSQL</productname>
> > +     19 or later. Before relying on it to prepare for a switchover or
> > +     failover, confirm that the publisher's version supports sequence
> > +     replication and that the sequences of interest are already known to the
> > +     subscription.
> >
> > Do you think above is necessary? At subscription creation time, a
> > publisher running a version earlier than PostgreSQL 19 cannot have a
> > publication containing all sequences, so the subscription cannot have
> > such sequences known to it. Furthermore, ALTER SUBSCRIPTION to change
> > publisher-connection will report an error if the required
> > prerequisites are not met. Therefore, asking users to separately
> > verify the publisher version and sequence membership seems redundant
> > to me.
>
> This scenario can be possible, Kuroda-san has reported a similar issue at [1].
>
> > 3)
> > +bool
> > +HasSubscriptionSequences(Oid subid)
> >
> > Do you think we can resuse HasSubscriptionTables instead of
> > duplicating the complete code? We can have
> > HasSubscriptionRelations(subid, bool *has_tables, bool
> > *has_sequences)) Or is it not worth?
>
> Since checking at set connection does not handle all the cases, now
> I'm checking this at refresh sequences because of which this code has
> been now removed.
>
> The attached patch has the changes for the same. This patch also fixes
> the issue reported by Kuroda-san at [1].
>

When this idea was discussed earlier, I had asked whether we could
move the version check from copy_sequences() to the apply worker
(ProcessSequencesForSync) before it starts sequnece sync worker. The
intention was to avoid repeatedly starting the sequence-sync worker
only for it to exit in copy_sequences(). The apply worker itself could
report an error similar to how the backend reports it during REFRESH.
Thoughts?

--

Commit msg says:
Sequence synchronization relies on pg_get_sequence_data(), which was
added in PostgreSQL 19.

IIRC, it is present in PG18 as well, no?

thanks
Shveta