Re: Logical Replication of sequences

Hi Vignesh,

I have a question about the subscriber-side behaviour of currval().

======

AFAIK it is normal for currval() to give error is nextval() has not
yet been called [1]

For example.
test_pub=# create sequence s1;
CREATE SEQUENCE
test_pub=# select * from currval('s1');
2024-08-01 07:42:48.619 AEST [24131] ERROR: currval of sequence "s1"
is not yet defined in this session
2024-08-01 07:42:48.619 AEST [24131] STATEMENT: select * from currval('s1');
ERROR: currval of sequence "s1" is not yet defined in this session
test_pub=# select * from nextval('s1');
nextval
---------
1
(1 row)

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

test_pub=#

~~~

OTOH, I was hoping to be able to use currval() at the subscriber=side
to see the current sequence value after issuing ALTER .. REFRESH
PUBLICATION SEQUENCES.

Unfortunately, it has the same behaviour where currval() cannot be
used without nextval(). But, on the subscriber, you probably never
want to do an explicit nextval() independently of the publisher.

Is this currently a bug, or maybe a quirk that should be documented?

For example:

Publisher
==========

test_pub=# create sequence s1;
CREATE SEQUENCE
test_pub=# CREATE PUBLICATION pub1 FOR ALL SEQUENCES;
CREATE PUBLICATION
test_pub=# select * from nextval('s1');
nextval
---------
1
(1 row)

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

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

test_pub=#

Subscriber
==========

(Notice currval() always gives an error unless nextval() is used prior).

test_sub=# create sequence s1;
CREATE SEQUENCE
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub'
PUBLICATION pub1;
2024-08-01 07:51:06.955 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 07:51:07.023 AEST [4211] LOG: logical
replication apply worker for subscription "sub1" has started
2024-08-01 07:51:07.037 AEST [4213] LOG: logical replication sequence
synchronization worker for subscription "sub1" has started
2024-08-01 07:51:07.063 AEST [4213] LOG: logical replication
synchronization for subscription "sub1", sequence "s1" has finished
2024-08-01 07:51:07.063 AEST [4213] LOG: logical replication sequence
synchronization worker for subscription "sub1" has finished

test_sub=# SELECT * FROM currval('s1');
2024-08-01 07:51:19.688 AEST [24325] ERROR: currval of sequence "s1"
is not yet defined in this session
2024-08-01 07:51:19.688 AEST [24325] STATEMENT: SELECT * FROM currval('s1');
ERROR: currval of sequence "s1" is not yet defined in this session
test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION SEQUENCES;
ALTER SUBSCRIPTION
test_sub=# 2024-08-01 07:51:35.298 AEST [4993] LOG: logical
replication sequence synchronization worker for subscription "sub1"
has started

test_sub=# 2024-08-01 07:51:35.321 AEST [4993] LOG: logical
replication synchronization for subscription "sub1", sequence "s1" has
finished
2024-08-01 07:51:35.321 AEST [4993] LOG: logical replication sequence
synchronization worker for subscription "sub1" has finished

test_sub=#
test_sub=# SELECT * FROM currval('s1');
2024-08-01 07:51:41.438 AEST [24325] ERROR: currval of sequence "s1"
is not yet defined in this session
2024-08-01 07:51:41.438 AEST [24325] STATEMENT: SELECT * FROM currval('s1');
ERROR: currval of sequence "s1" is not yet defined in this session
test_sub=#
test_sub=# SELECT * FROM nextval('s1');
nextval
---------
4
(1 row)

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

test_sub=#

======
[1] https://www.postgresql.org/docs/current/functions-sequence.html

Kind Regards,
Peter Smith.
Fujitsu Australia.