ALTER TABLE table RENAME TO sould change also sequence name

From: Mirek Hankus <M(dot)Hankus(at)ce3(dot)pl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: ALTER TABLE table RENAME TO sould change also sequence name
Date: 2003-08-08 12:27:29
Message-ID: 3F339731.70403@ce3.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2003-08-08 14:26:32 Re: upcoming 7.4 relaese: absent recode() function ?
Previous Message Ruslan A Dautkhanov 2003-08-08 05:17:32 upcoming 7.4 relaese: absent recode() function ?