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

Re: BUG #1883: Renaming a schema leaves inconsistent sequence

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Kouber Saparev <postgresql(at)saparev(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1883: Renaming a schema leaves inconsistent sequence
Date: 2005-09-23 02:13:30
Message-ID: 200509230213.j8N2DVv20251@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
This item has been added to the 8.1 bugs list:

	http://momjian.postgresql.org/cgi-bin/pgbugs

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


Kouber Saparev wrote:
> 
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

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

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-09-23 02:26:34
Subject: Re: Spinlocks, yet again: analysis and proposed patches
Previous:From: Tom LaneDate: 2005-09-23 02:05:57
Subject: Re: PCTFree Results

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-09-23 02:29:21
Subject: Re: BUG #1883: Renaming a schema leaves inconsistent sequence
Previous:From: Tom LaneDate: 2005-09-22 17:38:17
Subject: Re: char field <> or != to a value does not select where field is null ???

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