Re: RFD: schemas and different kinds of Postgres objects
От | Bill Studenmund |
---|---|
Тема | Re: RFD: schemas and different kinds of Postgres objects |
Дата | |
Msg-id | Pine.NEB.4.33.0201251631050.12100-100000@vespasia.home-net.internetconnect.net обсуждение исходный текст |
Ответ на | Re: RFD: schemas and different kinds of Postgres objects (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
On Fri, 25 Jan 2002, Tom Lane wrote: > Bill Studenmund <wrstuden@netbsd.org> writes: > > But we will have the ability to set the path per schema. > > ?? I don't follow that at all. A namespace is something that's referred > to by a search path, not vice versa. Or are you defining "schema" to > mean some higher-level concept that incorporates a search path of > multiple primitive namespaces? Maybe that could work, but I'm not sure > I see the point yet. Oh. That would make a difference. We've been talking past each other. SQL schemas, as I understand the spec, are both. A shema is a container that holds things like tables and views and functions (and for PostgreSQL operators and aggregates and I'd suggest index operators, etc.). It also can include a schema path specification, which defines the search path used by routines (stored procedures & functions) contained in that schema. So say I have schemas foo, bar, and baz. I can set the schema path for schema bar to be foo:bar:baz:IMPLIMENTATION_SCHEMA, and all routines in bar will look in those four schemas for types, functions and tables (and everything else we use the search path for). (*) IMPLIMENTATION_SCHEMA is required by the spec, and contains all the built-ins. It's be implimentation_schema for pg. Also, if you have a path that doesn't list it, the db is supposed to prepend it to the list. So when migrating an app from a schema-unaware PostgreSQL to a schema-aware one, if we create a schema for each user, and make each such schema path in all the other such schemas, we make it such that all of the procedures in those schemas act like they have a unified namespace. There also is also the concept of the CURRENT_PATH which is the schema path used for parsed queries (like ones typed into psql). I got lost in the spec trying to find what this is supposed to default to, but what I understand other DBs to do is your CURRENT_PATH is set to the path of the schema you log into. Add to this mix the default schema for user X is schema X (which I thought was in the spec but I can't find now), and let's look at that example again. Say we had users foo, bar and baz before. We made schemas foo, bar, and baz. We set the default paths for each of these schemas to foo:bar:baz:IMPLIMENTATION_SCHEMA. Now the routines in each of these schemas will see a unified namespace. Next, when we log in as users foo, bar, or baz, and our CURRENT_PATH ends up including the namespaces of the three original users. So now all of our submitted queries also see a unified namespace. So with a schema-savy backend, by adding PATH statements to the schemas that pull in all of the previous schemas, we can make the old app behave as if it had a unified namespace. Does that make sense? Take care, Bill P.S. does anyone need copies of the spec? I found pdf's on the web a while back..
В списке pgsql-hackers по дате отправления:
Следующее
От: Thomas LockhartДата:
Сообщение: Re: RFD: schemas and different kinds of Postgres objects