Re: [BUGS] BUG #1883: Renaming a schema leaves inconsistent - Mailing list pgsql-hackers

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@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 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@candle.pha.pa.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@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