Thread: Renaming schema's
I have tried to rename a schema using: test=# update pg_catalog.pg_namespace test-# set nspname = 'jochemd' test-# where nspname = 'public'; This didn't work out very well, the schema became invisible in pgAdminII, something I wouldn't want to do to our customers. (It was still accessible using a schemaname.tablename notation and appeared to work technically correct.) Is there any way to rename a schema? Jochem
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> I have tried to rename a schema using:
> test=# update pg_catalog.pg_namespace
> test-# set nspname = 'jochemd'
> test-# where nspname = 'public';
> This didn't work out very well, the schema became invisible in
> pgAdminII, something I wouldn't want to do to our customers.
I would expect that to work; perhaps the problem is pgAdminII's?
regards, tom lane
Tom Lane wrote: > Jochem van Dieten <jochemd@oli.tudelft.nl> writes: > >>I have tried to rename a schema using: >>test=# update pg_catalog.pg_namespace >>test-# set nspname = 'jochemd' >>test-# where nspname = 'public'; > > >>This didn't work out very well, the schema became invisible in >>pgAdminII, something I wouldn't want to do to our customers. > > I would expect that to work; perhaps the problem is pgAdminII's? After looking at some more databases it turned out that the public schema always has OID 2200. I would expect pgAdminII assuming that OID 2200 = public schema to be the cause of this problem (I tried messing with ACL's to no avail and changing the name of other schema's works). So the question becomes if OID 2200 is reserved or hardcoded in the backend for the public schema or if this assumption from pgAdminII is incorrect? Jochem
Jochem van Dieten wrote:
> After looking at some more databases it turned out that the public
> schema always has OID 2200. I would expect pgAdminII assuming that OID
> 2200 = public schema to be the cause of this problem (I tried messing
> with ACL's to no avail and changing the name of other schema's works).
>
> So the question becomes if OID 2200 is reserved or hardcoded in the
> backend for the public schema or if this assumption from pgAdminII is
> incorrect?
>
Are you sure it isn't an issue with your search path? e.g.:
regression=# select oid,* from pg_namespace ;
oid | nspname | nspowner | nspacl
--------+------------+----------+--------
11 | pg_catalog | 1 | {=U}
99 | pg_toast | 1 | {=}
2200 | public | 1 | {=UC}
16766 | pg_temp_1 | 1 |
822259 | MySchema | 1 |
(5 rows)
regression=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | foo | table | postgres
(1 row)
regression=# update pg_namespace set nspname='joe' where oid = 2200;
UPDATE 1
regression=# select oid,* from pg_namespace ;
oid | nspname | nspowner | nspacl
--------+------------+----------+--------
11 | pg_catalog | 1 | {=U}
99 | pg_toast | 1 | {=}
16766 | pg_temp_1 | 1 |
822259 | MySchema | 1 |
2200 | joe | 1 | {=UC}
(5 rows)
regression=# \dt
No relations found.
regression=# show search_path;
search_path
--------------
$user,public
(1 row)
regression=# set search_path to '$user','joe';
SET
regression=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
joe | foo | table | postgres
(1 row)
HTH,
Joe
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> After looking at some more databases it turned out that the public
> schema always has OID 2200.
That is what it's assigned during initdb.
> I would expect pgAdminII assuming that OID
> 2200 = public schema to be the cause of this problem (I tried messing
> with ACL's to no avail and changing the name of other schema's works).
I think it's a bad idea for it to assume that. There's no reason,
for example, why one shouldn't be able to drop and later recreate the
public schema. The public schema isn't magic at all, except that by
convention it's part of the standard search path --- and that's driven
off its name, not its OID.
regards, tom lane
Tom Lane wrote: > Jochem van Dieten <jochemd@oli.tudelft.nl> writes: > >>I would expect pgAdminII assuming that OID >>2200 = public schema to be the cause of this problem (I tried messing >>with ACL's to no avail and changing the name of other schema's works). > > I think it's a bad idea for it to assume that. There's no reason, > for example, why one shouldn't be able to drop and later recreate the > public schema. The public schema isn't magic at all, except that by > convention it's part of the standard search path --- and that's driven > off its name, not its OID. I asked on the pgadmin-support list and Dave Page answered: <quote> pgAdmin hides system objects by default, but in the case of the public schema it makes an exception bcause hiding public would not be sensible. It does it by a combination of name and OID: the oid is less than the last system oid, so it is hidden, except if it is called public. Switch on show System Objects on the view menu and you should see it. </quote> Switching on System Objects indeed makes the renamed schema visible in pgAdminII. Jochem
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
> I asked on the pgadmin-support list and Dave Page answered:
> <quote>
> pgAdmin hides system objects by default, but in the case of the public
> schema it makes an exception bcause hiding public would not be sensible.
> It does it by a combination of name and OID: the oid is less than the
> last system oid, so it is hidden, except if it is called public.
Hm. Might I suggest that a better policy (as of 7.3) would be:
1. Schemas (pg_namespace entries) are considered system objects if and
only if their names begin with 'pg_'.
2. For all object types that exist within schemas, it's a system object
if and only if it's within a system schema.
We really want to move away from using OID-range tests for anything...
regards, tom lane