RFD: schemas and different kinds of Postgres objects 
Author Message
 RFD: schemas and different kinds of Postgres objects

Quote:


> > When configured for historical behavior would need to:
> > 1. have search path: temp, any, system
> > 2. guard against duplicate table names across all schemas (except temp schema)

> This would be a *whole* lot simpler if we forgot the notion of "any"
> and made the search order look like

>    (temp, private, public, system)

> where the public namespace is world-writable but the private per-user
> ones are (typically at least) not.

[ I am just reading this schema thread now.]

The above private/public idea seems like a much better than 'any'.  That
'any' thing had me quite confused and the idea thought you would have
duplicates that would only be found at runtime seems destined to random
falures.

I assume 'private' above means search in my personal schema/namespace.

--
  Bruce Momjian                        |   http://www.***.com/

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Tue, 13 Jul 2004 11:46:08 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:
> > Or are you thinking about a per session behavior ?
> > I would rather envision a per database behavior.
> > Maybe the easy way out would be a "default creation schema" property for
> > each user, that would default to the username. If you want everything in one
> > schema simply alter the users.

> I hadn't really gotten to the point of thinking about exactly what and
> where the control knobs should be.  I suspect you are right that we will
> want the default behavior to be selectable on a per-user or per-database
> basis, which seems to eliminate the option of using GUC (at least in its
> current form).  We could easily add a field to pg_shadow or pg_database
> respectively to determine the default behavior.  It'd be nice though if
> the behavior could be changed after connection by a SET statement, which
> would be lots easier if the setting were GUC-controlled.  Peter, you see
> any way to resolve that?

I think we could set the database default at db creation time, then
allow SET to modify that default per session;  seems flexible enough.
It is basically a GUC value who's default is stored in pg_database
rather than postgresql.conf.   You could use postgresql.conf to set the
default schema type at db creation time.

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Tue, 13 Jul 2004 11:46:09 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> > This would be a *whole* lot simpler if we forgot the notion of "any"
> > and made the search order look like

> >       (temp, private, public, system)

I am starting to see the advantages and like it. I also like the exact
name "public" for the public schema.

Andreas

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Tue, 13 Jul 2004 19:44:33 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:


> > 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..

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Wed, 14 Jul 2004 09:43:37 GMT
 RFD: schemas and different kinds of Postgres objects
...

Quote:
> > Wouldn't it make sense to prefer operators/functions earlier in the search
> > path for resolving ambiguity.  So if you had plus(int4, int4) in my
> > schema and plus(int8, int8) in system, and they'd otherwise cause an
> > ambiguity failure for the query, use the plus(int4, int4) on mine. It
> > seems not too far from having the search path shadow later exact matches.
> Given the complexity of the resolution rules (cf.
> http://developer.postgresql.org/docs/postgres/typeconv.html),
> it's not clear that we can determine exactly which "later" entry ought
> to be blamed for causing a resolution failure.  I'd be interested to
> hear Lockhart's opinion on this --- but my gut feeling is we don't
> want to go there.  The resolution rules are already complicated enough,
> and I think layering an additional mechanism like that onto them might
> make the behavior totally unpredictable.

(I've been following the discussion; I suspect that this part may
already have an obvious answer since "any" scoping -- equivalent to
flattening the namespace? -- may now be out of favor; I'm assuming that
we have a clearly scoped lookup scheme available).

imho there is nothing fundamentally difficult or "unpredictable" about
layering schema lookup on to the existing function resolution rules. One
might want a bit better diagnostics about *which* function was actually
chosen, but reasonable scoping and lookup rules could be constructed
which give reasonable behavior with the addition of schemas.

For example, the current function resolution rules prefer an exact
match, then start looking for approximate matches, and narrow that down
to preferring the one with the best explicit match on data types. If
more than one matches, then it rejects the query. (I've left out one or
two steps, but on the whole this is the behavior that matters.)

With schemas, one could choose to use "closest schema" as the tiebreaker
for multiple matches, but istm that an exact match should always win.

We might want to include a mechanism that *blocks* schema lookups deeper
into the search path, to allow reliable *complete replacement* of a
function. This would be a property of the function, to be set when it is
defined in the schema. So an implementer could choose to restrict
lookups explicitly if that is deemed necessary. Again, this is not a
huge complication.

It is an interesting discussion, and the fine points will not be brought
out without having lots of back-and-forth, which seems to be happening
already ;)

                  - Thomas

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Wed, 14 Jul 2004 11:14:10 GMT
 RFD: schemas and different kinds of Postgres objects
Is *the path* below the same as "search path* in other
postings about this thread ?

Maybe Peter's posting isn't the one exactly what I have to
ask but there are too many postings for me to follow.

regards,
Hiroshi Inoue

Quote:

> Bill Studenmund writes:

> > Does SQL'99 say anything about this?

> Yes, though, as usual, you have to twist your brain a little to understand
> it.  My understanding is that for a function call of the form "foo(a, b)"
> it goes like this:

> 1. Find all functions named "foo" in the current database.  This is the
> set of "possibly candidate routines".

> 2. Drop all routines that you do not have EXECUTE privilege for.  This is
> the set of "executable routines".

> 3. Drop all routines that do not have compatible parameter lists.  This is
> the set of "invocable routines".

> 4. Drop all routines whose schema is not in the path.  This is the set of
> "candidate routines".

> 5. If you have more than one routine left, eliminate some routines
> according to type precedence rules.  (We do some form of this, SQL99
> specifies something different.)  This yields the set of "candidate subject
> routines".

> 6. Choose the routine whose schema is earliest in the path as the "subject
> routine".

> Execute the subject routine.  Phew!

> This doesn't look glaringly wrong to me, so maybe you want to consider it.
> Please note step 2.

> --


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Fri, 16 Jul 2004 09:44:27 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> Is *the path* below the same as "search path* in other
> postings about this thread ?

I think so. I believe the path I've been talking about is the one in step
6 below.

Take care,

Bill

Quote:
> Maybe Peter's posting isn't the one exactly what I have to
> ask but there are too many postings for me to follow.

> regards,
> Hiroshi Inoue


> > Bill Studenmund writes:

> > > Does SQL'99 say anything about this?

> > Yes, though, as usual, you have to twist your brain a little to understand
> > it.  My understanding is that for a function call of the form "foo(a, b)"
> > it goes like this:

> > 1. Find all functions named "foo" in the current database.  This is the
> > set of "possibly candidate routines".

> > 2. Drop all routines that you do not have EXECUTE privilege for.  This is
> > the set of "executable routines".

> > 3. Drop all routines that do not have compatible parameter lists.  This is
> > the set of "invocable routines".

> > 4. Drop all routines whose schema is not in the path.  This is the set of
> > "candidate routines".

> > 5. If you have more than one routine left, eliminate some routines
> > according to type precedence rules.  (We do some form of this, SQL99
> > specifies something different.)  This yields the set of "candidate subject
> > routines".

> > 6. Choose the routine whose schema is earliest in the path as the "subject
> > routine".

> > Execute the subject routine.  Phew!

> > This doesn't look glaringly wrong to me, so maybe you want to consider it.
> > Please note step 2.

> > --


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Sat, 17 Jul 2004 08:34:58 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:


> > Is *the path* below the same as "search path* in other
> > postings about this thread ?

> I think so. I believe the path I've been talking about is the one in step
> 6 below.

What I can find in SQL99 is SQL-path.
Does *the path*(i.e search path) mean SQL-path ?
They don't seem the same to me.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 17 Jul 2004 14:14:47 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:



> > > Is *the path* below the same as "search path* in other
> > > postings about this thread ?

> > I think so. I believe the path I've been talking about is the one in step
> > 6 below.

> What I can find in SQL99 is SQL-path.
> Does *the path*(i.e search path) mean SQL-path ?
> They don't seem the same to me.

While we may have not been using the terminology of the spec, I think we
have been talking about schema paths from SQL99.

One difference between our discussions and SQL99 I've noticed is that
we've spoken of having the path find functions (and operators and
aggregates), types, _and_tables_. SQL99 doesn't have tables in there
AFAICT, but I think it makes sense.

Take care,

Bill

---------------------------(end of broadcast)---------------------------



Sun, 18 Jul 2004 01:55:12 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:


> > What I can find in SQL99 is SQL-path.
> > Does *the path*(i.e search path) mean SQL-path ?
> > They don't seem the same to me.

> While we may have not been using the terminology of the spec, I think we
> have been talking about schema paths from SQL99.

> One difference between our discussions and SQL99 I've noticed is that
> we've spoken of having the path find functions (and operators and
> aggregates), types, _and_tables_.

My understanding is the same.
Tom, Peter is it right ?

Quote:
> SQL99 doesn't have tables in there
> AFAICT, but I think it makes sense.

It seems to make sense but they are different and
our *path* is never an extension of SQL-path.
Where are the difference or the relevance referred
to in this thread ?

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sun, 18 Jul 2004 08:25:37 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> [ just catching up on this thread after a couple days thinking about
> other things ]

> However, that doesn't bear very much on the general argument of the
> thread.  The bottom line is that we've put a whole lot of sweat into
> developing rules for resolving ambiguous operator and function calls,
> and I don't think we're going to be willing to toss all that effort into
> the s{*filter*}heap.  But making namespace search order the {*filter*} factor
> in choosing a function/operator (as Bill seems to want) would certainly
> break all that carefully-crafted effort.  If we put the system namespace
> at the front of the search list then users would be unable to override
> standard operators with schema-local substitutes; clearly that's no
> good.  But if we put it at the back, then a schema-local user operator
> would dominate all system entries of the same operator name, even for
> quite different types, and thereby it would break the resolution
> behavior.

I've changed my mind. :-)

Quote:
> So I'm still of the opinion that my original suggestion is the only
> workable one: collect candidates across all available namespaces,
> discarding only those that are exact matches to candidates in earlier
> namespaces, and then apply the existing resolution rules to the
> collection.  AFAICS this need not be any slower than what we do now,
> if the catalog is set up so that we can collect candidates in one
> indexscan without regard to namespace.  The case where there actually
> are any exact matches to discard should be uncommon, so we can deal with
> it later on in the resolution process.

Sounds like the thing to do, and it matches the spec. :-)

Oh, you can make a path with your namespace before the built-in one. It's
just that if you don't include the built-in one (IMPLIMENTATION_SCHEMA) in
a path, you're supposed to prepend it to the specified list.

Take care,

Bill

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Mon, 19 Jul 2004 05:25:44 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:



> >> While we may have not been using the terminology of the spec, I think we
> >> have been talking about schema paths from SQL99.

> >> One difference between our discussions and SQL99 I've noticed is that
> >> we've spoken of having the path find functions (and operators and
> >> aggregates), types, _and_tables_.

> > My understanding is the same.
> > Tom, Peter is it right ?

> SQL99's SQL-path is very clearly stated to be used only for looking up
> routines and user-defined type names.  Extending it to cover tables,
> operators, and so forth makes sense to me, but we have to recognize
> that it is a spec extension and therefore not all the answers we need
> can be found in the spec.

True. I think that extending the path to be used for operators and
aggregates makes sense as they are special types of function calls. The
searching for tables might need to be a configurable parameter (defaulting
to yes), though. I think it makes sense to do, but I can imagine cases
where apps need to not.

Quote:
> I also find it curious that they exclude standard type names from the
> search path.  It would seem obvious to treat the standard type names
> as included in a schema that is part of the search path, but AFAICT
> this is not done in the spec.  Postgres *has to* do it that way,
> however, or give up our whole approach to datatypes; surely we don't
> want to hardwire the SQL-standard datatypes into the parser to the
> exclusion of the not-so-standard ones.

> IMHO, the spec's artificial distinction between system and user types
> limits its usefulness as a guide to the questions we're debating here.

True.

Does SQL99 support types as flexable as the ones we do? I know types in
Oracle are basically special cases of already built-in ones...

Take care,

Bill

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 19 Jul 2004 07:55:34 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:




> > > SQL99 doesn't have tables in there
> > > AFAICT, but I think it makes sense.

> > It seems to make sense but they are different and
> > our *path* is never an extension of SQL-path.
> > Where are the difference or the relevance referred
> > to in this thread ?

> How is our path not an extention of SQL-path? Or at least how is the path
> I've been pushing not an SQL-path?

IMHO _tables_like objects must be guarded from such
a search mechanism fundamentally. I don't object to
the use of our *path* but it should be distinguished
from SQL-path.

For example the PATH environment variable is used
only to search executables not files. Is it
preferable for *rm a_file* to search all the directory
in the PATH ? If the purpose is different the different
*path* is needed of cource.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 19 Jul 2004 08:15:25 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:

> I have no objection to the point it makes sense to use
> such *path*s internally but I think it also has a siginificance
> for SQL-path to not look up _tables_like objects.
> I think they are different from the first and we should(need)
> not manage the system with one *path*.

I'm unconvinced.  We must search for datatypes and tables on the same
path because tables have associated datatypes; it will definitely not
do to look for a table's datatype and get the wrong type.  And I think
that functions and operators should be looked for on the same path
as datatypes, because a type should be pretty closely associated with
the functions/operators for it.  So it seems to me that the apparent
flexibility of having more than one path is just a way to shoot yourself
in the foot.  Why are you concerned that we keep them separate?

Quote:
> BTW I see few references to *catalog*. Would the concept
> of catalog be introduced together. If so what would be
> contained in the current database.

My thought is that we will consider catalog == database.  As far as
I can tell, that is a legitimate implementation-defined way of
interpreting the spec.  (It's not clear to me what the value is of
having more than one level of schema hierarchy; or at least, if you want
hierarchical namespaces, there's no argument for stopping at depth two.
But I digress.)  To satisfy the spec we must allow a (purely decorative)
specification of the current database name as the catalog level of a
qualified name, but that's as far as I want to go.  In this round,
anyway.  Cross-database access is not something to tackle for 7.3.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 19 Jul 2004 10:45:41 GMT
 RFD: schemas and different kinds of Postgres objects

Quote:


> > I have no objection to the point it makes sense to use
> > such *path*s internally but I think it also has a siginificance
> > for SQL-path to not look up _tables_like objects.
> > I think they are different from the first and we should(need)
> > not manage the system with one *path*.

> I'm unconvinced.  We must search for datatypes and tables on the same
> path because tables have associated datatypes;

Isn't the table definition a part of the datatype in
such a case ?

Quote:
> it will definitely not
> do to look for a table's datatype and get the wrong type.  And I think
> that functions and operators should be looked for on the same path
> as datatypes, because a type should be pretty closely associated with
> the functions/operators for it.  So it seems to me that the apparent
> flexibility of having more than one path is just a way to shoot yourself
> in the foot.  Why are you concerned that we keep them separate?

For example, doesn't 'DROP table a_table' drop the
a_table table in a schema in the *path* if there's
no a_table table in the current schema ?

If we would never introduce SQL-paths (in the future)
there would be problem.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 19 Jul 2004 11:45:17 GMT
 
 [ 18 post ]  Go to page: [1] [2]

 Relevant Pages 

1. RFD: schemas and different kinds of Postgres objects

2. Help - tkprof output different for 2 schemas with same objects

3. export/import objects to different schemas

4. Postgres Schemas Needed - Please!

5. Postgres Schemas Wanted - Please

6. Transferring data from a SQL server to a different kind of server

7. Universal stored procedure for inserting data in different kinds of tables

8. Seeking information on different kinds of databases.

9. A different kind of join

10. Designer 2.1 (Tables in different schemas)

11. How do you compare the data in different schemas not just table diffs

12. Do FROM items of different schemas conflict?


 
Powered by phpBB® Forum Software