Re: RE: [PATCHES] relation filename patch - Mailing list pgsql-hackers

On Mon, May 01, 2000 at 05:27:04PM -0400, Tom Lane wrote:
> 
> I was sort of envisioning a search path of schema names.  Temp table
> masking could be implemented by pushing the session-local schema onto
> the front of the search path.  Not sure how that relates to SQL3's ideas
> about schemas, however.
> 
> > So, relname cannot be enough. Isn't OID already sufficent though? I
> > thought oids are unique across the entire installation, not just a
> > particular db.
> 
> Er, well, no.  Consider pg_class, which has both the same name and the
> same OID in every DB in the installation --- but we have to treat it
> as separately instantiated in each DB.  Most of the system tables work
> that way.  OTOH we have a couple of top-level tables like pg_shadow,
> which are the same table for all DBs in the installation.
> 

Well, in schema-land this taken care of by the fact that all those system
tables live in a schema named information_schema, which is defined as
view on tables in the schema definition_schema. To some extent, our use
of pg_ for all the system tables simulates this.

> It could be that we ought to eliminate the whole notion of separate
> databases within an installation, or more accurately merge it with the
> concept of schemas.  Really, the existing database mechanism is sort
> of a limited implementation of schemas.
> 

See the discussion about this between Peter and I (and Jan?) last time
schemas came up. We agreed that pg's databases map to SQL92 Catalogs
rather nicely, with the whole installation being a 'cluster of catalogs'.
Now, If some one can explain to me what a 'module' is ...

> > In any case, the solution may be to convert relname
> > (+default or user supplied schema) to rel oid, as early as possible,
> > then indexing (and caching) on that.
> 
> Right, but there wouldn't be only one default schema, there'd be some
> kind of search path in which an unqualified relname would be sought.
> 

Perhaps, but that's an extension. SQL92 defines a default SCHEMA
for a session, which is set via the SET SCHEMA statement, strangely
enough. Having nesting SCHEMA's might be useful, but I'm not not sure
how. Getting any at all in there would help a lot. I'd suggest the
default be configurable on a per user basis. That'd allow some nifty
access controls, with just the existing VIEW code.

Turns out I needed these kind of schema's last week: I wanted to create
filtered access to a set of tables. Simple, right? Add booleans, create
views that test the boolean, remove SELECT privilege on the tables.
Only problem, is now I had to go and edit all 200+ SELECT statements
in the application code, to point at the views instead of the tables,
or rename every table, and edit the 200+ SELECTs in the other apps,
that do data entry and maintenance. If I had schema, I'd have changed
the default schema for the 'web' login, and created views that had the
same name as the tables, selecting back into the real tables in their own
schema. 10 mins, and done, don't touch the tested app. code at all! That's
what got me a round-toit, getting this patch off to be discussed.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005