Re: Renaming a table leaves orphaned implicit sequences which

Looks like this TODO item:

    o Have ALTER TABLE rename SERIAL sequences

Sorry we haven't fixed it yet.

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

Victor Sudakov wrote:
>
> Hello.
>
> I use PostgreSQL 7.4.2 on i386-portbld-freebsd5.2, compiled by GCC cc (GCC) 3.3.3 [FreeBSD] 20031106
>
> Description:
> It seems that renaming tables with columns of type "serial" leaves
> "orphaned" implicit sequences which breaks pg_restore.
>
> How to reproduce:
>
> 1. Create a table
>
> CREATE DATABASE something1;
> CREATE DATABASE something2;
> \c something1
> CREATE TABLE test1 (id serial, name char(12));
> ALTER TABLE test1 RENAME TO test2;
>
> 2. Run dump/restore and get an error:
>
> $ pg_dump -Fc something1 | pg_restore -d something2
> pg_restore: NOTICE:  CREATE TABLE will create implicit sequence "test2_id_seq" for "serial" column "test2.id"
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation "test1_id_seq" does not exist
>
> Workaround:
> Do not use the "serial" data type, always create sequences explicitly.
> pg_dump always generates a "CREATE SEQUENCE" clause for explicit
> sequences.
>
> --
> Victor Sudakov,  VAS4-RIPE, VAS47-RIPN
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  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