Обсуждение: altering the name of the public schema

[restarting the "Renaming schema's" thread from pgsql-general]

Using pgAdminII version 1.4.2 with PostgreSQL 7.3

I can alter the name of a schema using:
test=# update pg_catalog.pg_namespace
test-#     set nspname = 'newname'
test-#     where nspname = 'oldname';

This works without a problem until I try to rename the public schema.
For some reason this schema doesn't list among the schema's in a
database anymore. Changing the name back to public will make it list
again. But, even when it is not visible I can perform queries on it.
psql does not have any problems.

If I just add another entry into the pg_namespace table using an insert
query it will show straight away as a schema in pgAdminII. So I presume
the issue lies in the one thing I can't control in the pg_namespace
table; the OID.
Could it be that somewhere in pgAdminII there is a reference to the OID
2200 that is assigned to the public schema by default?

Jochem

> -----Original Message-----
> From: Jochem van Dieten [mailto:jochemd@oli.tudelft.nl]
> Sent: 02 December 2002 01:48
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] altering the name of the public schema
>
>
> [restarting the "Renaming schema's" thread from pgsql-general]


I don't read -general, but there was part of this discussion on
-hackers.

> Using pgAdminII version 1.4.2 with PostgreSQL 7.3
>
> I can alter the name of a schema using:
> test=# update pg_catalog.pg_namespace
> test-#     set nspname = 'newname'
> test-#     where nspname = 'oldname';
>
> This works without a problem until I try to rename the public schema.
> For some reason this schema doesn't list among the schema's in a
> database anymore. Changing the name back to public will make it list
> again. But, even when it is not visible I can perform queries on it.
> psql does not have any problems.
>
> If I just add another entry into the pg_namespace table using
> an insert
> query it will show straight away as a schema in pgAdminII. So
> I presume
> the issue lies in the one thing I can't control in the pg_namespace
> table; the OID.
> Could it be that somewhere in pgAdminII there is a reference
> to the OID
> 2200 that is assigned to the public schema by default?

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.

Regards, Dave.

Dave Page wrote:
> From: Jochem van Dieten [mailto:jochemd@oli.tudelft.nl]
>
>>Could it be that somewhere in pgAdminII there is a reference to the OID
>>2200 that is assigned to the public schema by default?
>
>
> 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.

That indeed solves this issue. May I make an enhancement request that
objects with an OID less than the last system OID are hidden, unless the
OID is 2200 (system OID's are not cycled, are they)?

Jochem