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

ALTER TABLE table RENAME TO sould change also sequence name

From
Mirek Hankus
Date:
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

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

From
Bruce Momjian
Date:
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