The following bug has been logged online:
Bug reference: 2583
Logged by: Morus Walter
Email address: morus.walter@experteer.de
PostgreSQL version: 8.1.4
Operating system: linux (i686)
Description: Problem with sql dump for renamed table containing
columns of type serial
Details:
When I
- create a database
- create a table containing a column of type serial
- grant rights on this table and the autogenerated
sequence for the serial
- rename this table
- dump the database using pg_dump
the created sql dump recreates the sequence with a name apropriate for the
renamed table while the rights are restored for the sequence as it was named
initially.
E.g.
create table foo ( id sequence );
creates a sequence foo_id_seq
After renaming foo to bar the sequence is still foo_id_seq.
If the database is dumped, the dump contains
create table bar ( id sequence );
which creates a sequence bar_id_seq but rights on that
sequence are restored on foo_id_seq which fails.
I found two related bug reports, one claiming that
backup/restore does not work if table and sequence
use different schemas
(http://archives.postgresql.org/pgsql-bugs/2006-07/msg00021.php) the other
describing that problem when the column is renamed
(http://archives.postgresql.org/pgsql-bugs/2006-03/msg00106.php).