Re: Renaming a table leaves orphaned implicit sequences which

From: Paul Tillotson <pntil(at)shentel(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Renaming a table leaves orphaned implicit sequences which
Date: 2004-04-06 01:47:25
Message-ID: 40720C2D.9060700@shentel.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-04-06 05:07:25 Re: bug in 7.4.2, with Handling of Double Quotation Marks
Previous Message Peter Eisentraut 2004-04-06 00:16:48 Re: Problem starting postgresql !!