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

Renaming a table leaves orphaned implicit sequences which breaks pg_restore.

From: Victor Sudakov <sudakov(at)sibptus(dot)tomsk(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Renaming a table leaves orphaned implicit sequences which breaks pg_restore.
Date: 2004-04-01 06:49:41
Message-ID: 20040401064941.GA11257@sibptus.tomsk.ru (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: Stephen FrostDate: 2004-04-02 20:16:38
Subject: No error-checking on binary timestamp
Previous:From: Enke, MichaelDate: 2004-03-31 17:33:13
Subject: Re: BUG #1110: psql -h localhost consults the nameservice

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