Re: Logical Replication of sequences

On Wed, 29 Oct 2025 at 09:19, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> Hi Vignesh,
>
> Some review comments patch V20251029-0001 (the test code only)
>
> ======
> src/test/subscription/t/036_sequences.pl
>
> 1.
> +##########
> +## ALTER SUBSCRIPTION ... REFRESH PUBLICATION with (copy_data = off) should
> +# not update the sequence values for the new sequence.
> +##########
> +
> +# Create a new sequence 'regress_s4'
> +$node_publisher->safe_psql(
> + 'postgres', qq(
> + CREATE SEQUENCE regress_s4;
> + INSERT INTO regress_seq_test SELECT nextval('regress_s4') FROM
> generate_series(1,100);
> +));
> +
>
> AFAICT the sequence `regress_s3` (from the previous test part) was
> already a "new sequence" that had not yet been REFRESHED to the
> subscriber. So I think maybe there wasn't any need to create another
> sequence `regress_s4` for this test part.

Modified

> ~~~
>
> 2.
> +# Check - newly published sequence values are not updated
> +$result = $node_subscriber->safe_psql(
> + 'postgres', qq(
> + SELECT last_value, log_cnt, is_called FROM regress_s4;
> +));
>
> Maybe that comment can give more details:
> # Check - newly published sequence values are not updated when (copy_data = off)

Modified

> ~~~
>
> 3.
> +##########
> +# ALTER SUBSCRIPTION ... REFRESH PUBLICATION should report an error when:
> +# a) sequence definitions differ between the publisher and subscriber, or
> +# b) a sequence is missing on the publisher.
> +##########
>
> OK, you have these mismatch parameters and missing sequences test for
> "REFRESH PUBLICATION", but what about doing the same tests for
> "REFRESH SEQUENCES" -- e,g, I am thinking you can ALTER/DROP some
> publication that previously had synchronized OK, to verify what
> happens during "REFRESH SEQUENCES".

I'm planning to do this in a later version.

The changes for the same are available in the v20251029_2 version
patch attached at [1].
[1] - https://www.postgresql.org/message-id/CALDaNm18siwD6Mamv8Dd8ubwSCw3Fi6SnB4B3Lr%2B4R7snLkfeA%40mail.gmail.com

Regards,
Vignesh