Re: Logical Replication of sequences

On Wed, 19 Jun 2024 at 20:33, vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Tue, 18 Jun 2024 at 16:10, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> >
> > Agreed and I am not sure which is better because there is a value in
> > keeping the state name the same for both sequences and tables. We
> > probably need more comments in code and doc updates to make the
> > behavior clear. We can start with the sequence state as 'init' for
> > 'needs-to-be-sycned' and 'ready' for 'synced' and can change if others
> > feel so during the review.
>
> Here is a patch which does the sequence synchronization in the
> following lines from the above discussion:
> This commit introduces sequence synchronization during 1) creation of
> subscription for initial sync of sequences 2) refresh publication to
> synchronize the sequences for the newly created sequences 3) refresh
> publication sequences for synchronizing all the sequences.
> 1) During subscription creation with CREATE SUBSCRIPTION (no syntax change):
> - The subscriber retrieves sequences associated with publications.
> - Sequences are added in the 'init' state to the pg_subscription_rel table.
> - Sequence synchronization worker will be started if there are any
> sequences to be synchronized
> - A new sequence synchronization worker handles synchronization in
> batches of 100 sequences:
> a) Retrieves sequence values using pg_sequence_state from the publisher.
> b) Sets sequence values accordingly.
> c) Updates sequence state to 'READY' in pg_susbcripion_rel
> d) Commits batches of 100 synchronized sequences.
> 2) Refreshing sequences with ALTER SUBSCRIPTION ... REFRESH
> PUBLICATION (no syntax change):
> - Stale sequences are removed from pg_subscription_rel.
> - Newly added sequences in the publisher are added in 'init' state
> to pg_subscription_rel.
> - Sequence synchronization will be done by sequence sync worker as
> listed in subscription creation process.
> - Sequence synchronization occurs for newly added sequences only.
> 3) Introduce new command ALTER SUBSCRIPTION ... REFRESH PUBLICATION
> SEQUENCES for refreshing all sequences:
> - Removes stale sequences and adds newly added sequences from the
> publisher to pg_subscription_rel.
> - Resets all sequences in pg_subscription_rel to 'init' state.
> - Initiates sequence synchronization for all sequences by sequence
> sync worker as listed in subscription creation process.

Here is an updated patch with a few fixes to remove an unused
function, changed a few references of table to sequence and added one
CHECK_FOR_INTERRUPTS in the sequence sync worker loop.

Regards,
Vignesh