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(at)postgresql(dot)org)
>

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