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
Attachments:
v1-0001-Fix-pg_get_sequence_data-showing-incorrect-last_v.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Fix-pg_get_sequence_data-showing-incorrect-last_v.patchDownload
From d80a07a7a1ada926af31de180abc00240dd61ad7 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Wed, 20 Aug 2025 16:10:17 +0530
Subject: [PATCH v1] Fix pg_get_sequence_data showing incorrect last_value
Fix pg_get_sequence_data showing incorrect last_value for a newly
created sequence.
---
src/backend/commands/sequence.c | 6 +++++-
src/bin/pg_dump/pg_dump.c | 2 +-
2 files changed, 6 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index a3c8cff97b0..896f862c0d9 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1836,7 +1836,11 @@ pg_get_sequence_data(PG_FUNCTION_ARGS)
seq = read_seq_tuple(seqrel, &buf, &seqtuple);
- values[0] = Int64GetDatum(seq->last_value);
+ if (seq->is_called)
+ values[0] = Int64GetDatum(seq->last_value);
+ else
+ isnull[0] = true;
+
values[1] = BoolGetDatum(seq->is_called);
UnlockReleaseBuffer(buf);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index fc7a6639163..4defae1a59f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -19175,8 +19175,8 @@ dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo)
entry = bsearch(&key, sequences, nsequences,
sizeof(SequenceItem), SequenceItemCmp);
- last = entry->last_value;
called = entry->is_called;
+ last = called ? entry->last_value : entry->startv;
}
resetPQExpBuffer(query);
--
2.43.0
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]: https://commitfest.postgresql.org/55/ -- Michael
want in the result of pg_get_sequence_data().
[1]: https://commitfest.postgresql.org/55/ -- Michael
--
Michael