The following bug has been logged online:
Bug reference: 1279
Logged by: adrian kierman
Email address: adriankierman@engineer.com
PostgreSQL version: 7.4.1
Operating system: fedora2
Description: pg_dump/resore has trouble with implicit sequences of
renamed tables
Details:
Thanks for a brilliant database - pg_dump/restore succesfully handled my 2
gig database transfer except for an issue with implicit sequences.
Steps to recreate the bug
1. create a table with a serial data type and implicit sequence
2. rename the table to something else
3. select nextval a couple of times on the sequence to advace it
4. dump and restore it to a different db
you'll notice that the sequence of the restored table is set to zero (or
rather is_called=false). Thus the value of the sequence has not been loaded.
In addition stored procedures making use of the original sequence will not
work as the implicit sequence created on resore had a different name based
on the now modified table name.
potential solution: have pg_dump dump out the sequences explicitly instead
of implicitly. -or- create an alter sequence name and use it to set the name
back to the old db's sequence name before calling setval I dont know the
backend well enough to know the potential issues with these aproaches.
once again thanks for postgres