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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kouber Saparev <postgresql(at)saparev(dot)com>,pgsql-bugs(at)postgresql(dot)org,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: BUG #1883: Renaming a schema leaves inconsistent sequence
Date: 2005-09-23 02:59:43
Message-ID: 200509230259.j8N2xhW25785@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > This item has been added to the 8.1 bugs list:
> > 	http://momjian.postgresql.org/cgi-bin/pgbugs
> 
> This isn't going to be fixed for 8.1.  I think it's really a variant of
> the TODO item
> 	o %Have ALTER TABLE RENAME rename SERIAL sequence names

Well, it might be a variant, but its failure is much worse.  For a table
rename, you just get a strange \d display:
	
	test=> CREATE TABLE test (x SERIAL);
	NOTICE:  CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x"
	CREATE TABLE
	test=> ALTER TABLE test RENAME TO test2;
	ALTER TABLE
	test=> INSERT INTO test2 VALUES (DEFAULT);
	INSERT 0 1
	test=> \d test2
	                          Table "public.test2"
	 Column |  Type   |                      Modifiers
	--------+---------+-----------------------------------------------------
	 x      | integer | not null default nextval('public.test_x_seq'::text)

The insert into the table still works.  For the schema rename, the
insert into the table doesn't work anymore.  The odds that a schema
rename is going to have _no_ sequence dependencies in the same schema
seems pretty unlikely, meaning rename schema is almost guarantted to
create some broken table defaults.  With this behavior, if we can't fix
it in 8.1, I am wonderingf we should just disable the feature:

	test=> CREATE SCHEMA aa;
	CREATE SCHEMA
	test=> CREATE TABLE aa.test (x SERIAL);
	NOTICE:  CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x"
	CREATE TABLE
	test=> ALTER SCHEMA aa RENAME TO bb;
	ALTER SCHEMA
	test=> INSERT INTO bb.test VALUES (DEFAULT);
	ERROR:  SCHEMA "aa" does NOT exist
	test=> \d bb.test
	                          Table "bb.test"
	 Column |  Type   |                    Modifiers
	--------+---------+-------------------------------------------------
	 x      | integer | not null default nextval('aa.test_x_seq'::text)

-- 
  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 03:04:23
Subject: Re: pg_config/share_dir
Previous:From: Josh BerkusDate: 2005-09-23 02:55:13
Subject: Re: PCTFree Results

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2005-09-23 03:12:28
Subject: Re: BUG #1883: Renaming a schema leaves inconsistent sequence
Previous:From: Tom LaneDate: 2005-09-23 02:29:21
Subject: Re: BUG #1883: Renaming a schema leaves inconsistent sequence

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