Re: ALTER TABLE table RENAME TO sould change also sequence name

TODO has:

o Have ALTER TABLE rename SERIAL sequences

However, I didn't realize the seriousness of the problem.

---------------------------------------------------------------------------

Mirek Hankus wrote:
>
> 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
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073