Re: Logical Replication of sequences - Mailing list pgsql-hackers

Hi Vignesh,

I noticed that when replicating sequences (using the latest patches
0730_2*)  the subscriber-side checks the *existence* of the sequence,
but apparently it is not checking other sequence attributes.

For example, consider:

Publisher: "CREATE SEQUENCE s1 START 1 INCREMENT 2;" should be a
sequence of only odd numbers.
Subscriber: "CREATE SEQUENCE s1 START 2 INCREMENT 2;" should be a
sequence of only even numbers.

Because the names match, currently the patch allows replication of the
s1 sequence. I think that might lead to unexpected results on the
subscriber. IMO it might be safer to report ERROR unless the sequences
match properly (i.e. not just a name check).

Below is a demonstration the problem:

==========
Publisher:
==========

(publisher sequence is odd numbers)

test_pub=# create sequence s1 start 1 increment 2;
CREATE SEQUENCE
test_pub=# select * from nextval('s1');
 nextval
---------
       1
(1 row)

test_pub=# select * from nextval('s1');
 nextval
---------
       3
(1 row)

test_pub=# select * from nextval('s1');
 nextval
---------
       5
(1 row)

test_pub=# CREATE PUBLICATION pub1 FOR ALL SEQUENCES;
CREATE PUBLICATION
test_pub=#

==========
Subscriber:
==========

(subscriber sequence is even numbers)

test_sub=# create sequence s1 start 2 increment 2;
CREATE SEQUENCE
test_sub=# SELECT * FROM nextval('s1');
 nextval
---------
       2
(1 row)

test_sub=# SELECT * FROM nextval('s1');
 nextval
---------
       4
(1 row)

test_sub=# SELECT * FROM nextval('s1');
 nextval
---------
       6
(1 row)

test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub'
PUBLICATION pub1;
2024-08-01 08:43:04.198 AEST [24325] WARNING:  subscriptions created
by regression test cases should have names starting with "regress_"
WARNING:  subscriptions created by regression test cases should have
names starting with "regress_"
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
test_sub=# 2024-08-01 08:43:04.294 AEST [26240] LOG:  logical
replication apply worker for subscription "sub1" has started
2024-08-01 08:43:04.309 AEST [26244] LOG:  logical replication
sequence synchronization worker for subscription "sub1" has started
2024-08-01 08:43:04.323 AEST [26244] LOG:  logical replication
synchronization for subscription "sub1", sequence "s1" has finished
2024-08-01 08:43:04.323 AEST [26244] LOG:  logical replication
sequence synchronization worker for subscription "sub1" has finished

(after the CREATE SUBSCRIPTION we are getting replicated odd values
from the publisher, even though the subscriber side sequence was
supposed to be even numbers)

test_sub=# SELECT * FROM nextval('s1');
 nextval
---------
       7
(1 row)

test_sub=# SELECT * FROM nextval('s1');
 nextval
---------
       9
(1 row)

test_sub=# SELECT * FROM nextval('s1');
 nextval
---------
      11
(1 row)

(Looking at the description you would expect odd values for this
sequence to be impossible)

test_sub=# \dS+ s1
                             Sequence "public.s1"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     2 |       1 | 9223372036854775807 |         2 | no      |     1

======
Kind Regards,
Peter Smith.
Fujitsu Australia