ALTER TABLE schema SCHEMA TO new_schema?

Joe Conway <mail@joeconway.com> writes:

Someone asked earlier about how to change a bunch of existing tables int the
PUBLIC schema to some other schema. For grins I tried:
regression=# update pg_class set relnamespace=556829 where relname = 'foo' and
relnamespace=2200;
UPDATE 1

and it seemed to work fine (i.e. moved foo from schema public to schema bar).

But it didn't fix the pg_depend entries linking the table to its schema :-(

But it made me wonder if we shouldn't have:
ALTER TABLE table SCHEMA TO new_schema

I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y

I don't see anything in the SQL spec about this; anyone know what
precedent is in Oracle or other DBMSes?

regards, tom lane

Tom Lane wrote:

Joe Conway <mail@joeconway.com> writes:

Someone asked earlier about how to change a bunch of existing tables int the
PUBLIC schema to some other schema. For grins I tried:
regression=# update pg_class set relnamespace=556829 where relname = 'foo' and
relnamespace=2200;
UPDATE 1

and it seemed to work fine (i.e. moved foo from schema public to schema bar).

But it didn't fix the pg_depend entries linking the table to its schema :-(

Yeah, I knew there was something I was forgetting. That's why I didn't
actually offer it up as a solution to anyone.

But it made me wonder if we shouldn't have:
ALTER TABLE table SCHEMA TO new_schema

I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y

I don't see anything in the SQL spec about this; anyone know what
precedent is in Oracle or other DBMSes?

Good question. I can't find anything in the Oracle docs indicating it is even
possible. We should probably just go with your suggestion. Anything else
beyond the relnamespace and pg_depend entries that need to be dealt with?

Joe

I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y

I don't see anything in the SQL spec about this; anyone know what
precedent is in Oracle or other DBMSes?

Good question. I can't find anything in the Oracle docs indicating it is

even

possible. We should probably just go with your suggestion. Anything else
beyond the relnamespace and pg_depend entries that need to be dealt with?

What about sequences for serial columns? What about views or types that
depend on the table?

Chris

Christopher Kings-Lynne wrote:

possible. We should probably just go with your suggestion. Anything else
beyond the relnamespace and pg_depend entries that need to be dealt with?

What about sequences for serial columns? What about views or types that
depend on the table?

Yeah, good point. I think properly dealing with the pg_depends issues will
catch anything of that nature, but what to do with them?

Probably should move dependent type, constraint, index entries to the same new
namespace. We might want to move related sequences, but I'm not sure we'd want
to do that silently, since the sequence could be in use for other tables as
well. And we should probably restrict the change if there are dependent
functions or views. Does this capture the issues?

Joe

Am Sonntag, 1. Dezember 2002 06:47 schrieb Tom Lane:

Joe Conway <mail@joeconway.com> writes:

Someone asked earlier about how to change a bunch of existing tables int
the PUBLIC schema to some other schema. For grins I tried:
regression=# update pg_class set relnamespace=556829 where relname =
'foo' and relnamespace=2200;
UPDATE 1

and it seemed to work fine (i.e. moved foo from schema public to schema
bar).

But it didn't fix the pg_depend entries linking the table to its schema :-(

But it made me wonder if we shouldn't have:
ALTER TABLE table SCHEMA TO new_schema

I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y

I don't see anything in the SQL spec about this; anyone know what
precedent is in Oracle or other DBMSes?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Here is, what DB2 has to offer:

DB2: Syntax
DB2:
DB2: .-TABLE-.
DB2: >>-RENAME--+-------+--table-name--TO--new-table-identifier-----><
DB2:
DB2: Description
DB2:
DB2: |table-name
DB2: Names the existing table that is to be renamed. The name, including the
DB2: schema name, must identify a table that already exists in the database
DB2: (SQLSTATE 42704). It can be an alias identifying the table. It must not
DB2: be the name of a catalog table (SQLSTATE 42832), a summary table, a
DB2: typed table (SQLSTATE 42997), a nickname, or an object of other than
DB2: table or alias (SQLSTATE 42809).
DB2:
DB2: |new-table-identifier
DB2: |Specifies the new name for the table without a schema name. The |schema
DB2: name of the table-name is used to qualify the new name for the |table.
DB2: The qualified name must not identify a table, view, |or alias that
DB2: already exists in the database (SQLSTATE 42710).

It looks like it is not possible to move a table from one schema to another.
ALTER TABLE don't handle schemas either.

But I like the "RENAME a.x to b.x"-syntax.

Tommi

--
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de

Joe Conway wrote:

Christopher Kings-Lynne wrote:

possible. We should probably just go with your suggestion. Anything else
beyond the relnamespace and pg_depend entries that need to be dealt
with?

What about sequences for serial columns? What about views or types that
depend on the table?

Yeah, good point. I think properly dealing with the pg_depends issues
will catch anything of that nature, but what to do with them?

Probably should move dependent type, constraint, index entries to the
same new namespace. We might want to move related sequences, but I'm not
sure we'd want to do that silently, since the sequence could be in use
for other tables as well. And we should probably restrict the change if
there are dependent functions or views. Does this capture the issues?

Why not just leave the sequence and types in the original schema and
make sure the table refers to them _there_? We just need to make sure
we have schema qualified references to the sequences and types.

Indexes, triggers (and constraints), toast tables etc. are related to
just one table so they can migrate together, I think.

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9

Tommi Maekitalo wrote:

But I like the "RENAME a.x to b.x"-syntax.

And we would not be creating a new syntax, but just changing the
semantics of an existing one to be schema-aware. Still an extension
that should be noted in the docs, but less intrusive.

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9

Fernando Nasser wrote:

Why not just leave the sequence and types in the original schema and
make sure the table refers to them _there_? We just need to make sure
we have schema qualified references to the sequences and types.

Well, the type entry for the relation *is* related to just one table, so I'd
be inclined to move it also. But leaving the sequence alone might be the best
thing to do. Although, I think sequences created via SERIAL are dropped with
their referencing table now, aren't they?

test=# create table myserial(id serial);
NOTICE: CREATE TABLE will create implicit sequence 'myserial_id_seq' for
SERIAL column 'myserial.id'
CREATE TABLE
test=# \ds myserial_id_seq
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+----------
public | myserial_id_seq | sequence | postgres
(1 row)

test=# drop table myserial;
DROP TABLE
test=# \ds myserial_id_seq
No matching relations found.

Maybe that's an argument that they ought to also move to the new schema when
the dependency exists.

Indexes, triggers (and constraints), toast tables etc. are related to
just one table so they can migrate together, I think.

I agree.

Joe

I wonder if the sequences created by SERIAL should not be going into a
pg_sequence schema and protected like the toast tables are.

One could still share sequences by explicitly creating them and using a
DEFAULT clause with nextval().

We could even stop printing that annoying NOTICE ;-)

Regards,
Fernando

Joe Conway wrote:

Fernando Nasser wrote:

Why not just leave the sequence and types in the original schema and
make sure the table refers to them _there_? We just need to make sure
we have schema qualified references to the sequences and types.

Well, the type entry for the relation *is* related to just one table, so
I'd be inclined to move it also. But leaving the sequence alone might be
the best thing to do. Although, I think sequences created via SERIAL are
dropped with their referencing table now, aren't they?

test=# create table myserial(id serial);
NOTICE: CREATE TABLE will create implicit sequence 'myserial_id_seq'
for SERIAL column 'myserial.id'
CREATE TABLE
test=# \ds myserial_id_seq
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+----------
public | myserial_id_seq | sequence | postgres
(1 row)

test=# drop table myserial;
DROP TABLE
test=# \ds myserial_id_seq
No matching relations found.

Maybe that's an argument that they ought to also move to the new schema
when the dependency exists.

Indexes, triggers (and constraints), toast tables etc. are related to
just one table so they can migrate together, I think.

I agree.

Joe

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9

We could even stop printing that annoying NOTICE ;-)

Agreed with this part :)

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Yeah, good point. I think properly dealing with the pg_depends issues will
catch anything of that nature, but what to do with them?

Probably should move dependent type, constraint, index entries to the same

new

namespace. We might want to move related sequences, but I'm not sure we'd

want

to do that silently, since the sequence could be in use for other tables

as

well. And we should probably restrict the change if there are dependent
functions or views. Does this capture the issues?

Why just restrict them to moving tables? What if someone wants to move a
function or an aggregate to another schema?

What if they want to copy it?

Chris

Why just restrict them to moving tables? What if someone wants to move a
function or an aggregate to another schema?

What if they want to copy it?

Copying might be tricky, but I'd be happy to help with moving everything
else around. Though I don't think sequences can move (until we can
properly track their dependencies) but everything else should be able
to.

Copy is another story all together. But I'd like a

CREATE SCHEMA ... AS COPY <schemaname>;

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor wrote:

Why just restrict them to moving tables? What if someone wants to move a
function or an aggregate to another schema?

What if they want to copy it?

Copying might be tricky, but I'd be happy to help with moving everything
else around. Though I don't think sequences can move (until we can
properly track their dependencies) but everything else should be able
to.

Copy is another story all together. But I'd like a

CREATE SCHEMA ... AS COPY <schemaname>;

Wouldn't it be better to use pg_dump/pg_restore for that?

If we could ask for just oen/some of the non-system schemas to be dumped
it would be easy to restore it as another or even move it to another
database. And one could dump only the schema or schema+data, as needed.
Of course, dependencies would have to be handled as objects can refer to
objects in other schemas.

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9

Copy is another story all together. But I'd like a

CREATE SCHEMA ... AS COPY <schemaname>;

Wouldn't it be better to use pg_dump/pg_restore for that?

Perhaps.. But I'd really like to see some of these types of abilities
added to pg_admin.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc