Thread: Bug #699: pg_dump not reporting correct start value for sequence

Martin Ellis (martin@myinternet.com.au) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
pg_dump not reporting correct start value for sequence

Long Description
It appears, when a sequence is incremented that value of start, as reported by pg_dump, seems to be set to 1.  I had
expectedto be set to the value set the 'create sequence' command regardless of the current sequence value. 

How to reporduce:

sh: psql my_test
my_test=# create sequence my_test start 100;
CREATE
my_test=# select * from my_test;
 sequence_name | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled |
is_called

---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 my_test       |        100 |            1 | 9223372036854775807 |         1 |           1 |       1 | f         | f

my_test=# \q
sh: pg_dump my_test
\\connect - martin

--
-- TOC Entry ID 20 (OID 17718)
--
-- Name: my_test Type: SEQUENCE Owner: martin
--

CREATE SEQUENCE "my_test" start 100 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

connect - martin

--
-- TOC Entry ID 21 (OID 17718)
--
-- Name: my_test Type: SEQUENCE SET Owner: martin
--

SELECT setval ('"my_test"', 100, false);

sh: psql my_test
my_test=# select nextval('my_test');
 nextval
---------
     100
(1 row)

my_test=#\q
sh: pg_dump my_test
\connect - martin

--
-- TOC Entry ID 20 (OID 17718)
--
-- Name: my_test Type: SEQUENCE Owner: martin
--

CREATE SEQUENCE "my_test" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

\connect - martin

--
-- TOC Entry ID 21 (OID 17718)
--
-- Name: my_test Type: SEQUENCE SET Owner: martin
--

SELECT setval ('"my_test"', 100, true);


-------------------

As you can see from the two dumps the although the value of the sequence remains correct, the start value does not.

This causes problems for me as I use the dump to check if the schema is up to date.

Martin Ellis

Sample Code


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> This causes problems for me as I use the dump to check if the schema is up to date.

Then don't do that, or more accurately try looking at the setval not the
CREATE SEQUENCE.

The START value of a sequence isn't saved anywhere; it's merely the
initial value of the running counter, and so pg_dump cannot reliably
reconstruct it for you.  I do not consider this a bug.  pg_dump's
charter is to reproduce the database state when the script is reloaded,
and it does so.

            regards, tom lane