Skip site navigation (1) Skip section navigation (2)

9.2beta1 regression: pg_restore --data-only does not set sequence values any more

From: Martin Pitt <mpitt(at)debian(dot)org>
To: PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: 9.2beta1 regression: pg_restore --data-only does not set sequence values any more
Date: 2012-05-15 12:25:00
Message-ID: 20120515122500.GD3041@piware.de (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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: 

 * Create a db and sequence:

  $ createdb test
  $ psql test -c "CREATE SEQUENCE odd10 INCREMENT BY 2 MINVALUE 1 MAXVALUE 10 CYCLE"
  CREATE SEQUENCE
 
 * 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:
 
   $ dropdb test

 * Restore DB:

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

 * 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

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

Responses

pgsql-hackers by date

Next:From: Noah MischDate: 2012-05-15 13:08:08
Subject: Re: Analyzing foreign tables & memory problems
Previous:From: Tom LaneDate: 2012-05-15 02:43:35
Subject: Re: Bugs in our Windows socket code

pgsql-bugs by date

Next:From: lrDate: 2012-05-15 12:27:10
Subject: BUG #6640: pg_dump does not always dump data of tables marked aseditable in extension
Previous:From: Tom LaneDate: 2012-05-15 10:58:22
Subject: Re: Error with refering to the header files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group