Thread: Renaming tables to other schemas

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?

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

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

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

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

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