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

[Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>]

With the following errors caused by ALTER SCHEMA RENAME, I recommend we
remove this feature for 8.1 and revisit it for 8.2.  I would just remove
the grammar construct for it and the documentation.

To fix this, we would need to redesign the way we store DEFAULT sequence
assignments, and I don't think that is a good thing to do during beta. 
I see people wanting bitmapped scans ASAP, not renaming of schemas.  Our
beta time is better spent on other things than getting this to work now.

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

Bruce Momjian wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 

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