Thread: Indexes, TOAST tables, and namespaces

Indexes, TOAST tables, and namespaces

From
Tom Lane
Date:
I'm trying to work out what to do with indexes in the context of
schemas.

As of today's CVS tip, what the code does is that CREATE INDEX can only
specify an unqualified index name, and the index is automatically
created in the same namespace as its parent table.  Thus, index names
still have to be distinct from each other and from regular table names,
but only within a namespace (schema) not globally over the whole
database.

I seem to recall someone claiming that the SQL spec requires indexes to
be in a different namespace from tables --- ie, index names and table
names should never conflict, period.  I can't find any evidence of this
in the spec; AFAICT it doesn't mention the concept of indexes at all.
But perhaps this is standard industry practice (what do Oracle and other
DBMSes do?).  We could imagine creating an "auxiliary namespace" for
each regular namespace in which to put indexes, if anyone thinks that's
worthwhile.  Thoughts?

In any case, I intend to remove the current prohibition against user
table names starting with "pg_".  Instead there will be a prohibition
against user schema names starting with "pg_"; but within a user schema
you can call your tables whatever you like.  The existing protection
restrictions associated with IsSystemRelationName() calls will migrate
over to instead be tests on which namespace contains the table in
question.

The system catalogs will still be named pg_xxx, but will live in
namespace "pg_catalog"; TOAST tables will still be named "pg_toast_xxx",
but will live in namespace "pg_toast".  This should minimize the
disruption to client applications that look at the catalogs.  There'll
also be temporary namespaces "pg_temp_xxx" to house temporary tables.

Comments, objections, better ideas?
        regards, tom lane