Re: Logical Replication of sequences

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.

Regards,
Vignesh