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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2005-09-16 05:02:14 | BUG #1885: SHOW autovacuum settings tab completion broken |
Previous Message | John R Pierce | 2005-09-15 03:34:01 | Re: bug on starting postgres |
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2005-09-15 14:57:05 | Re: pgplsql temporary tables |
Previous Message | Maximiliano Di Rienzo | 2005-09-15 14:05:48 | pgplsql temporary tables |