Re: [BUGS] BUG #1883: Renaming a schema leaves inconsistent sequence

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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
implicitsequence "test_x_seq" for serial column "test.x"CREATE TABLEtest=> ALTER TABLE test RENAME TO test2;ALTER
TABLEtest=>INSERT INTO test2 VALUES (DEFAULT);INSERT 0 1test=> \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 SCHEMAtest=> CREATE TABLE aa.test (x SERIAL);NOTICE:  CREATE TABLE will create implicit
sequence"test_x_seq" for serial column "test.x"CREATE TABLEtest=> ALTER SCHEMA aa RENAME TO bb;ALTER SCHEMAtest=>
INSERTINTO bb.test VALUES (DEFAULT);ERROR:  SCHEMA "aa" does NOT existtest=> \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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073