9.2beta1 regression: pg_restore --data-only does not set sequence values any more - Mailing list pgsql-bugs

Hello all,

while packaging 9.2 beta 1 for Debian/Ubuntu the postgresql-common
test suite noticed a regression: It seems that pg_restore --data-only
now skips the current value of sequences, so that in the upgraded
database the sequence counter is back to the default. This can lead to
rather serious data errors.

Reproducer:=20

 * Create a db and sequence:

  $ createdb test
  $ psql test -c "CREATE SEQUENCE odd10 INCREMENT BY 2 MINVALUE 1 MAXVALUE =
10 CYCLE"
  CREATE SEQUENCE
=20
 * Advance it two steps:

   $ psql --cluster 9.2/main -Atc "SELECT nextval('odd10')" test
   1
   $ psql --cluster 9.2/main -Atc "SELECT nextval('odd10')" test
   3

 * Dump schema and "test" data:

   $ pg_dumpall -s > /tmp/schema
   $ pg_dump -Fc test > /tmp/test.dump

 * Drop db:
=20
   $ dropdb test

 * Restore DB:

   $ psql template1 < /tmp/schema=20
   $ pg_restore --data-only -d postgres /tmp/test.dump=20

 * Check the counter:

   $ psql -Atc "SELECT nextval('odd10')" test
   1

This is wrong, it should be "5", from the original database. This
worked all the way up to 9.1.


With "pg_restore /tmp/test.dump" (i. e. a full dump), you see

   SELECT pg_catalog.setval('odd10', 3, true);

in the dump, but it is not present in the output of
"pg_restore --data-only /tmp/test.dump". However, when I use the 9.1
version, it works:

   $ /usr/lib/postgresql/9.1/bin/pg_restore --data-only  /tmp/test.dump  | =
grep setval
   SELECT pg_catalog.setval('odd10', 3, true);

Thanks!

Martin

--=20
Martin Pitt                        | http://www.piware.de
Ubuntu Developer (www.ubuntu.com)  | Debian Developer  (www.debian.org)