Re: ALTER TABLE table RENAME TO sould change also sequence name - Mailing list pgsql-bugs

From Bruce Momjian
Subject Re: ALTER TABLE table RENAME TO sould change also sequence name
Date
Msg-id 200308110302.h7B32mf05257@candle.pha.pa.us
Whole thread Raw
In response to ALTER TABLE table RENAME TO sould change also sequence name  (Mirek Hankus <M.Hankus@ce3.pl>)
List pgsql-bugs
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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: vacuum is not sufficient?
Next
From: sad
Date:
Subject: feature request