Skip site navigation (1) Skip section navigation (2)

Re: Renaming a table leaves orphaned implicit sequences which

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Victor Sudakov <sudakov(at)sibptus(dot)tomsk(dot)ru>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Renaming a table leaves orphaned implicit sequences which
Date: 2004-05-20 00:16:44
Message-ID: 200405200016.i4K0Gil24858@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2004-05-20 01:40:05
Subject: Re: BUG #1144: comments don't get propagated
Previous:From: Gyenese Pál AttilaDate: 2004-05-19 16:54:34
Subject: int4, int8, real ....division...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group