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

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