Thread

1. Renaming tables to other schemas

   Ziga <ziga@ljudmila.org> — 2004-02-07T15:04:22Z

   Hello!
   
   The following SQL works:
   
   ALTER TABLE a.foo RENAME TO bar;
   
   But the following doesn't:
   
   ALTER TABLE a.foo RENAME TO b.bar;
   
   The capability to move objects to other schemas
   would be quite useful.
   
   Apparently, everything works OK if you change
   pg_class.relnamespace with UPDATE, but this is
   not very nice.
   
   Are there any problems to be expected with this that
   I am missing and if not, would it make sense to support
   changing schemas with ALTER TABLE?
   
   
   

2. Re: Renaming tables to other schemas

   Neil Conway <neilc@samurai.com> — 2004-02-15T06:34:42Z

   ziga@ljudmila.org writes:
   > The capability to move objects to other schemas would be quite
   > useful.
   
   I agree. It's not utterly-trivial to implement (for one thing, you
   need to move any dependant objects like indexes to the new schema),
   but some form of this functionality would be a useful thing to add,
   IMHO.
   
   Bruce, can we add this to the TODO list?
   
   -Neil
   
   
   

3. Re: Renaming tables to other schemas

   Rod Taylor <rbt@rbt.ca> — 2004-02-15T12:53:50Z

   On Sun, 2004-02-15 at 01:34, Neil Conway wrote:
   > ziga@ljudmila.org writes:
   > > The capability to move objects to other schemas would be quite
   > > useful.
   > 
   > I agree. It's not utterly-trivial to implement (for one thing, you
   > need to move any dependant objects like indexes to the new schema),
   > but some form of this functionality would be a useful thing to add,
   > IMHO.
   
   It's not that hard to do either (I've done about 100 tables by hand at
   this point).
   
   Anyway, this should be supported by all RENAME commands, not just ALTER
   TABLE.
   -- 
   Rod Taylor <rbt [at] rbt [dot] ca>
   
   Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
   PGP Key: http://www.rbt.ca/rbtpub.asc
   

4. Re: Renaming tables to other schemas

   Bruce Momjian <pgman@candle.pha.pa.us> — 2004-02-17T15:37:46Z

   Rod Taylor wrote:
   -- Start of PGP signed section.
   > On Sun, 2004-02-15 at 01:34, Neil Conway wrote:
   > > ziga@ljudmila.org writes:
   > > > The capability to move objects to other schemas would be quite
   > > > useful.
   > > 
   > > I agree. It's not utterly-trivial to implement (for one thing, you
   > > need to move any dependant objects like indexes to the new schema),
   > > but some form of this functionality would be a useful thing to add,
   > > IMHO.
   > 
   > It's not that hard to do either (I've done about 100 tables by hand at
   > this point).
   > 
   > Anyway, this should be supported by all RENAME commands, not just ALTER
   > TABLE.
   
   Added TODO:
   
           o Allow the schema of objects to be changed
   
   -- 
     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
   
   

5. Re: Renaming tables to other schemas

   Robert Treat <xzilla@users.sourceforge.net> — 2004-02-20T15:13:00Z

   On Sunday 15 February 2004 07:53, Rod Taylor wrote:
   > On Sun, 2004-02-15 at 01:34, Neil Conway wrote:
   > > ziga@ljudmila.org writes:
   > > > The capability to move objects to other schemas would be quite
   > > > useful.
   > >
   > > I agree. It's not utterly-trivial to implement (for one thing, you
   > > need to move any dependant objects like indexes to the new schema),
   > > but some form of this functionality would be a useful thing to add,
   > > IMHO.
   >
   > It's not that hard to do either (I've done about 100 tables by hand at
   > this point).
   >
   > Anyway, this should be supported by all RENAME commands, not just ALTER
   > TABLE.
   
   Rod, can you lay out some psdueo code / logic involved in the process?  I'm 
   guessing you lock the entry in pg_class, you up dependent objects, lock them, 
   update them all... is there more to it?
   
   Robert Treat
   -- 
   Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
   
   

6. Re: Renaming tables to other schemas

   Rod Taylor <pg@rbt.ca> — 2004-02-20T15:44:16Z

   > Rod, can you lay out some psdueo code / logic involved in the process?  I'm 
   > guessing you lock the entry in pg_class, you up dependent objects, lock them, 
   > update them all... is there more to it?
   
   It was one an offline database at the time with only a single user -- so
   locking wasn't a concern at the time.
   
   To change the namespace of a table, update the namespace ID for:
   
   pg_class -> of table
   pg_type -> of table
   pg_class -> index(es) on table
   pg_type -> of indexes on table
   pg_constraint -> all constraints on table
   pg_depend -> dependencies of above objects on the namespace
   
   We didn't have inheritance or the more exotic items, but I seem to
   recall the views continued to work as expected with no changes.
   
   Dump and restore gave us what we expected, and the database functions as
   expected but that doesn't necessarily mean the above covers all items.