Postgresql 7.3.4 on Linux.
Problem is that when you create a table with serial type, it creates
sequence with coresponding name. Then you can grant some rights to
it (table and sequence), and after that change table name. From now on
you will not be able to restore such database, because name of sequence
is not changed.
pg_dump dumps databese without CREATE SEQUENCE statements(it marks field
as SERIAL so it is
automatically created) but with
GRANT xxx ON sequence_name TO someone
where sequence_name corresponds to first name of the table. So when you
try to restore such backup pg_restore will fail. It is not a serious bug
(it can be fixed during restoring), but some users may have problem with
it.
Here is a sample wich illustrates this bug:
aaa=# CREATE TABLE test1 (a SERIAL);
NOTICE: CREATE TABLE will create implicit sequence 'test1_a_seq' for
SERIAL column 'test1.a'
CREATE TABLE
aaa=# GRANT ALL ON test1 TO PUBLIC;
GRANT
aaa=# GRANT ALL ON test1_a_seq TO PUBLIC;
GRANT
aaa=# ALTER TABLE test1 RENAME to test2;
ALTER TABLE
aaa=#
And when you dump such database and try to restore it you will see
SET
NOTICE: CREATE TABLE will create implicit sequence 'test2_a_seq' for
SERIAL column 'test2.a'
CREATE TABLE
REVOKE
GRANT
ERROR: Relation "test1_a_seq" does not exist
ERROR: Relation "test1_a_seq" does not exist
ERROR: Relation "test1_a_seq" does not exist
Best regards
Mirek Hankus
