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

BUG #1883: Renaming a schema leaves inconsistent sequence names

From: "Kouber Saparev" <postgresql(at)saparev(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1883: Renaming a schema leaves inconsistent sequence names
Date: 2005-09-15 14:30:10
Message-ID: 20050915143010.92DA1F108F@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
The following bug has been logged online:

Bug reference:      1883
Logged by:          Kouber Saparev
Email address:      postgresql(at)saparev(dot)com
PostgreSQL version: 8.0.3
Operating system:   Linux 2.6.11.4
Description:        Renaming a schema leaves inconsistent sequence names
Details: 

When I rename a schema, all the serial fields are pointing to the old
schema, which no longer exists. So trying to insert new records fails.

Here there is an example:

------ begin ------

bugs=# create schema sch1;
CREATE SCHEMA

bugs=# create table sch1.test (id serial primary key, name char(1)) without
oids;
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial
column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
CREATE TABLE

bugs=# \d sch1.test
                             Table "sch1.test"
 Column |     Type     |                     Modifiers
--------+--------------+----------------------------------------------------

 id     | integer      | not null default nextval('sch1.test_id_seq'::text)
 name   | character(1) |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

bugs=# insert into sch1.test (name) values ('a');
INSERT 0 1

bugs=# alter schema sch1 rename to sch2;
ALTER SCHEMA

bugs=# \d sch2.test
                             Table "sch2.test"
 Column |     Type     |                     Modifiers
--------+--------------+----------------------------------------------------

 id     | integer      | not null default nextval('sch1.test_id_seq'::text)
 name   | character(1) |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

bugs=# insert into sch2.test (name) values ('b');
ERROR:  schema "sch1" does not exist

------ end ------

As you see, the default value of the serial field is pointing to a sequence
in schema "sch1" which is now "sch2". Changing the default value manually
fixes the problem, but it's not very convenient in case when there are a lot
of tables.

After I looked over the bugs submitted so far, I've found that the problem
is already reported, but I'm not sure whether it's well described there.
Take a look at 
- http://article.gmane.org/gmane.comp.db.postgresql.bugs/3033/

I apologize, if it is a known bug.

Regards,
Kouber Saparev

Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2005-09-15 14:57:05
Subject: Re: pgplsql temporary tables
Previous:From: Maximiliano Di RienzoDate: 2005-09-15 14:05:48
Subject: pgplsql temporary tables

pgsql-bugs by date

Next:From: Robert TreatDate: 2005-09-16 05:02:14
Subject: BUG #1885: SHOW autovacuum settings tab completion broken
Previous:From: John R PierceDate: 2005-09-15 03:34:01
Subject: Re: bug on starting postgres

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