Horton

Re: Logical Replication of sequences

vignesh C <vignesh21@gmail.com>

View thread
From: vignesh C <vignesh21@gmail.com>
To: Peter Smith <smithpb2250@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Euler Taveira <euler@eulerto.com>, Michael Paquier <michael@paquier.xyz>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Hou, Zhijie/侯 志杰 <houzj.fnst@fujitsu.com>, "Jonathan S. Katz" <jkatz@postgresql.org>
Date: 2024-08-05T04:44:03Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Add documentation for sequence synchronization.

  2. Remove unused assignment in CREATE PUBLICATION grammar.

  3. Add seq_sync_error_count to subscription statistics.

  4. Fix few issues in commit 5509055d69.

  5. Add sequence synchronization for logical replication.

  6. Add worker type argument to logical replication worker functions.

  7. Introduce "REFRESH SEQUENCES" for subscriptions.

  8. Refactor logical worker synchronization code into a separate file.

  9. Standardize use of REFRESH PUBLICATION in code and messages.

  10. Add "ALL SEQUENCES" support to publications.

  11. Expose sequence page LSN via pg_get_sequence_data.

  12. Resume conflict-relevant data retention automatically.

  13. Post-commit review fixes for 228c370868.

  14. Generate GUC tables from .dat file

On Thu, 1 Aug 2024 at 03:33, Peter Smith <smithpb2250@gmail.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