Re: Logical Replication of sequences

On Thu, 1 Aug 2024 at 03:33, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> 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?

The currval returns the most recent value obtained from the nextval
function for a given sequence within the current session. This
function is specific to the session, meaning it only provides the last
sequence value retrieved during that session. However, if you call
currval before using nextval in the same session, you'll encounter an
error stating "currval of the sequence is not yet defined in this
session." Meaning even in the publisher this value is only visible in
the current session and not in a different session. Alternatively you
can use the following to get the last_value of the sequence: SELECT
last_value FROM sequence_name. I feel this need not be documented as
the similar issue is present in the publisher and there is an "SELECT
last_value FROM sequence_name" to get the last_value.

Regards,
Vignesh