Обсуждение: pg_get_sequence_data Shows Non-NULL last_value for Freshly Created Sequence

Hi,

I noticed an inconsistency in the behavior of sequence-related
functions for a freshly created sequence.
CREATE SEQUENCE s1;

postgres=# select last_value from pg_sequences;
 last_value
------------

(1 row)

postgres=# select pg_sequence_last_value('s1');
 pg_sequence_last_value
------------------------

(1 row)

postgres=# select las_value from pg_get_sequence_data('s1');
 last_value
------------
          1
(1 row)

As you can see:

pg_sequences and pg_sequence_last_value return NULL for last_value,
which aligns with the expectation that the sequence hasn't been used
yet. However, pg_get_sequence_data returns the start value (1) even
though is_called is false. This seems inconsistent. I felt
pg_get_sequence_data should also return NULL for last_value in this
case to match the others.
Attached patch has a fix for the same. Thoughts?

Regards,
Vignesh

On Wed, Aug 20, 2025 at 07:16:55PM +0530, vignesh C wrote:
> pg_sequences and pg_sequence_last_value return NULL for last_value,
> which aligns with the expectation that the sequence hasn't been used
> yet. However, pg_get_sequence_data returns the start value (1) even
> though is_called is false. This seems inconsistent. I felt
> pg_get_sequence_data should also return NULL for last_value in this
> case to match the others.
> Attached patch has a fix for the same. Thoughts?

This function returns the values in the sequence tuple, primarily for
pg_dump (see commit bd15b7d).  IIUC your patch would break pg_dump on v18
and newer versions.

-- 
nathan

On Wed, Aug 20, 2025 at 11:47:38AM -0500, Nathan Bossart wrote:
> This function returns the values in the sequence tuple, primarily for
> pg_dump (see commit bd15b7d).  IIUC your patch would break pg_dump on v18
> and newer versions.

Concretely, after the following commands, the patch causes pg_dump to call
setval with the wrong value:

    CREATE SEQUENCE test;
    ALTER SEQUENCE test RESTART WITH 2;

Without patch:
    SELECT pg_catalog.setval('public.test', 2, false);

With patch:
    SELECT pg_catalog.setval('public.test', 1, false);

-- 
nathan

Nathan Bossart <nathandbossart@gmail.com> writes:
> This function returns the values in the sequence tuple, primarily for
> pg_dump (see commit bd15b7d).  IIUC your patch would break pg_dump on v18
> and newer versions.

The proposed patch includes a change to pg_dump that I suppose is
meant to compensate.  But I'm not 100% sure that it does so correctly.
Anyway, given that it's supposed to provide low-level inspection of
the sequence tuple, I think that it's best not to be too cute.

            regards, tom lane

On Wed, Aug 20, 2025 at 01:01:52PM -0400, Tom Lane wrote:
> The proposed patch includes a change to pg_dump that I suppose is
> meant to compensate.  But I'm not 100% sure that it does so correctly.
> Anyway, given that it's supposed to provide low-level inspection of
> the sequence tuple, I think that it's best not to be too cute.

The patch goes against the original promise of pg_get_sequence_data()
to "blindly" report the contents of the sequence tuple, so as
decisions can be taken in the frontend and not enforced in the
backend, which is what this patch is trying to introduce.

Also, FWIW, the current behavior of the function also matters around
[1], as this makes it easier for my AM callbacks to report what they
want in the result of pg_get_sequence_data().

[1]: https://commitfest.postgresql.org/55/
--
Michael