Re: [BUGS] Renaming a table leaves orphaned implicit sequences which

#1

I got bitten by this one also. Perhaps it would be possible to change
pg_dump so that it dumps the create table statement with the explicit
sequence, rather than the original SQL used to create the table? (This
would preserve old dumps and the syntactical sugar which I would not
want to forego.)

Paul Tillotson

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#2

Looks like this TODO item:

o Have ALTER TABLE rename SERIAL sequences

Sorry we haven't fixed it yet.

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

Victor Sudakov wrote:

Quote:

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.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] Renaming a table leaves orphaned implicit sequences which

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] Renaming a table leaves orphaned implicit sequences which - 04-05-2004 , 11:58 PM

Re: [BUGS] Renaming a table leaves orphaned implicit sequences which - 05-19-2004 , 07:21 PM