Re: Logical Replication of sequences

On Thu, Jul 25, 2024 at 9:06 AM vignesh C <vignesh21@gmail.com> wrote:
>
> The attached v20240725 version patch has the changes for the same.

Thank You for addressing the comments. Please review below issues:

1) Sub ahead of pub due to wrong initial sync of last_value for
non-incremented sequences. Steps at [1]
2) Sequence's min value is not honored on sub during replication. Steps at [2]

[1]:
-----------
on PUB:
CREATE SEQUENCE myseq001 INCREMENT 5 START 100;
SELECT * from pg_sequences; -->shows last_val as NULL

on SUB:
CREATE SEQUENCE myseq001 INCREMENT 5 START 100;
SELECT * from pg_sequences; -->correctly shows last_val as NULL
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
SELECT * from pg_sequences;  -->wrongly updates last_val to 100; it is
still NULL on Pub.

Thus , SELECT nextval('myseq001') on pub gives 100, while on sub gives 105.
-----------


[2]:
-----------
Pub:
CREATE SEQUENCE myseq0 INCREMENT 5 START 10;
SELECT * from pg_sequences;

Sub:
CREATE SEQUENCE myseq0 INCREMENT 5 MINVALUE 100;

Pub:
SELECT nextval('myseq0');
SELECT nextval('myseq0');

Sub:
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
--check 'last_value', it is 15 while min_value is 100
SELECT * from pg_sequences;
-----------

thanks
Shveta