wrong sequence value in dump file
Hi,
When dumping with pg_dump and the directory option, I got a sequence value
that is greater in the dump file than the sequence in the source database.
The sequence is defined as:
CREATE SEQUENCE id_seq_test
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1900000000000000000
START 100000000000
CACHE 1
NO CYCLE;
the cache is 1 then the problem seems not linked with the cache.
The value in the toc.dat file
000377c0: 0000 004b 0000 0053 454c 4543 5420 7067 5f63 6174 616c 6f67 2e73
6574 7661 6c28 2773 3033 3574 3030 2e69 645f 7365 715f 706c 6175 735f 7669
6f6c 272c 2031 3030 ...K...SELECT
pg_catalog.setval('myschema.id_seq_test', 100
00037800: 3030 3032 3839 3332 312c 2074 7275 6529 3b0a 0101 0000 0001 0100
0000 0007 0000 0073 3033 3574 3030 0101 0000 0001 0100 0000 000f 0000 006f
5f73 3033 3574 3030 100000289321, true);..............
select last_value from id_seq_test;
┌──────────────┐
│ last_value │
├──────────────┤
│ 100000289290 │
└──────────────┘
Any explanation will be very appreciate
Fabrice
Fabrice Chapuis <fabrice636861@gmail.com> writes:
When dumping with pg_dump and the directory option, I got a sequence value
that is greater in the dump file than the sequence in the source database.
Perhaps you were dumping from a standby server? It's normal for
a standby to show a value up to 32 counts more than the primary,
because of some tricks that sequence.c plays to reduce the number
of WAL records generated for nextval().
regards, tom lane
Ok, effectively the dump comes from a standby. Thanks for your explanation.
Best regards,
Fabrice
On Wed, Jul 16, 2025 at 5:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Fabrice Chapuis <fabrice636861@gmail.com> writes:
When dumping with pg_dump and the directory option, I got a sequence
value
that is greater in the dump file than the sequence in the source
database.
Perhaps you were dumping from a standby server? It's normal for
a standby to show a value up to 32 counts more than the primary,
because of some tricks that sequence.c plays to reduce the number
of WAL records generated for nextval().regards, tom lane