Re: Oracle Style packages on postgres

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, May 11, 2005 10:55 AM
> To: Dave Held
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Oracle Style packages on postgres
>
>
> "Dave Held" <dave(dot)held(at)arraysg(dot)com> writes:
> > The rule is simple: when the identifier has
> > more than two parts, search for the first part among the schemas
^^^^^^^^^^^^^^^^^^^
> > first, and then the catalogs.
>
> This doesn't actually work, because there is already ambiguity as to
> which level the first name is. See for instance the comments in
> transformColumnRef().

I don't follow. switch (numnames) case 3 is unambiguous under either
syntax. case 1 and 2 are unchanged under my proposed rules. It's
really only case 4+ that is affected. And the change is as follows:

if (numnames > MAX_SCHEMA_DEPTH + 3)
{
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("improper qualified name (too many dotted names): %s",
NameListToString(cref->fields))));
return NULL;
}
switch (numnames)
{
case 1: ...
case 2: ...
case 3: ...
default:
{
char* name[MAX_SCHEMA_DEPTH + 3];
char** i;
char** end = name + numnames;
char* colname = name + numnames - 1;
for (i = name; i != end; ++i)
{
/* definition of lnth() should be easy enough to infer */
*i = strVal(lnth(cref->fields));
}

/*
* We check the catalog name and then ignore it.
*/
if (!isValidNamespace(name[0]))
{
if (strcmp(name[0], get_database_name(MyDatabaseId)) != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cross-database references are not implemented: %s",
NameListToString(cref->fields))));
i = name + 1;
numnames -= 3;
}
else
{
i = name;
numnames -= 2; }
/*
* isValidNamespace() should work like LookupExplicitNamespace()
* except that it should return false on failure instead of
* raising an error
*/

/* Whole-row reference? */
if (strcmp(end[-1], "*") == 0)
{
node = transformWholeRowRef(pstate, i, numnames, end[-2]);
break;
}
/*
* Here I've changed the signature of transformWholeRowRef() to
* accept a char** and an int for the schema names
*/

/* Try to identify as a twice-qualified column */
node = qualifiedNameToVar(pstate, i, numnames, end[-1], true);
/*
* And obviously we have to hack qualifiedNameToVar() similarly
*/
if (node == NULL)
{
/* Try it as a function call */
node = transformWholeRowRef(pstate, i, numnames, end[-2]);
node = ParseFuncOrColumn(pstate,
list_make1(makeString(end[-1])),
list_make1(node),
false, false, true);
}
break;
}
}

What am I missing?

__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East, Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129

"Dave Held" <dave(dot)held(at)arraysg(dot)com> writes:
> /*
> * We check the catalog name and then ignore it.
> */
> if (!isValidNamespace(name[0]))
> {
> if (strcmp(name[0], get_database_name(MyDatabaseId)) != 0)
> ereport(ERROR,

Which more or less proves my point: the syntax is fundamentally
ambiguous. I suppose people would learn not to use schema names that
match the database they are in, but that doesn't make it a good idea to
have sensible behavior depend on non-overlap of those names.

[ thinks for awhile ... ]

OTOH, what if we pretended that two-level-nested schemas ARE catalogs
in the sense that the SQL spec expects? Then we could get rid of the
pro-forma special case here, which isn't ever likely to do anything more
useful than throw an error anyway. Thus, we'd go back to the pre-7.3
notion that the current Postgres DB's name isn't part of the SQL naming
scheme at all, and instead handle the spec's syntax requirements by
setting up some conventions that make a schema act like what the spec
says is a catalog.

There are some nontrivial issues to be thought about here, like under
what conditions "CREATE SCHEMA foo" ought to create a top-level schema
versus creating a schema under some other schema that we are pretending
is the active "catalog". But it seems on first glance like something
could be worked out.

regards, tom lane

> There are some nontrivial issues to be thought about here, like under
> what conditions "CREATE SCHEMA foo" ought to create a top-level schema
> versus creating a schema under some other schema that we are pretending
> is the active "catalog". But it seems on first glance like something
> could be worked out.

Just go the extra info and call the top level catalogs in the commands
as well:

CREATE DATABASE mydb;
\c mydb

CREATE CATALOG foo;
CREATE SCHEMA foo.bar
CREATE TABLE foo.bar.baz (bif serial);
--

On Wed, 2005-05-11 at 15:41 -0400, Rod Taylor wrote:
> > There are some nontrivial issues to be thought about here, like under
> > what conditions "CREATE SCHEMA foo" ought to create a top-level schema
> > versus creating a schema under some other schema that we are pretending
> > is the active "catalog". But it seems on first glance like something
> > could be worked out.
>
> Just go the extra info and call the top level catalogs in the commands

Extra inch, not info.

> as well:
>
> CREATE DATABASE mydb;
> \c mydb
>
> CREATE CATALOG foo;
> CREATE SCHEMA foo.bar
> CREATE TABLE foo.bar.baz (bif serial);
--

Rod Taylor <pg(at)rbt(dot)ca> writes:
>> There are some nontrivial issues to be thought about here, like under
>> what conditions "CREATE SCHEMA foo" ought to create a top-level schema
>> versus creating a schema under some other schema that we are pretending
>> is the active "catalog". But it seems on first glance like something
>> could be worked out.

> Just go the extra info and call the top level catalogs in the commands
> as well:

Nope, doesn't meet the spec requirements. One thing we can certainly
say is that there would have to be a notion of an "active catalog"
(which could be determined by outside-the-spec means, perhaps a GUC
variable) because "CREATE SCHEMA foo" would have to create foo as a
child of the active catalog.

I'm also fairly unclear on what this implies for search_path searches.
Currently, as soon as you have more than one dotted name, search_path
is ignored ... but should it be used? Maybe "a.b" ought to be sought
as "foo.a.b" for successive values of "foo" from the search path.

regards, tom lane

Tom Lane wrote:
> Rod Taylor <pg(at)rbt(dot)ca> writes:
> >> There are some nontrivial issues to be thought about here, like under
> >> what conditions "CREATE SCHEMA foo" ought to create a top-level schema
> >> versus creating a schema under some other schema that we are pretending
> >> is the active "catalog". But it seems on first glance like something
> >> could be worked out.
>
> > Just go the extra info and call the top level catalogs in the commands
> > as well:
>
> Nope, doesn't meet the spec requirements. One thing we can certainly
> say is that there would have to be a notion of an "active catalog"
> (which could be determined by outside-the-spec means, perhaps a GUC
> variable) because "CREATE SCHEMA foo" would have to create foo as a
> child of the active catalog.
>
> I'm also fairly unclear on what this implies for search_path searches.
> Currently, as soon as you have more than one dotted name, search_path
> is ignored ... but should it be used? Maybe "a.b" ought to be sought
> as "foo.a.b" for successive values of "foo" from the search path.

How is a catalog different from a schema?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> How is a catalog different from a schema?

In the spec there's a hard-wired difference: catalogs contain schemas,
schemas don't contain other schemas. The idea at hand here is to make
our namespaces serve both purposes. (I knew there was a good reason
not to use the word "schema" for namespaces ;-)) The spec behavior
would be met by using exactly two levels of namespace, but there
wouldn't be anything stopping people from using more, except that their
queries wouldn't look like spec-compatible queries.

There are a number of issues that would have to be solved to make this
actually work, but on first glance it seems like a possibly attractive
idea.

Besides, I can't wait to hear the moans from the newsysviews crew when
the implications of this sink in ;-) ;-)

regards, tom lane

On Wed, May 11, 2005 at 04:49:52PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > How is a catalog different from a schema?
>
> In the spec there's a hard-wired difference: catalogs contain schemas,
> schemas don't contain other schemas. The idea at hand here is to make
> our namespaces serve both purposes. (I knew there was a good reason
> not to use the word "schema" for namespaces ;-)) The spec behavior
> would be met by using exactly two levels of namespace, but there
> wouldn't be anything stopping people from using more, except that their
> queries wouldn't look like spec-compatible queries.

So is the *only* difference in which contains the other? It sounds like
they just use a different name to enforce that there's only 2 levels.

> Besides, I can't wait to hear the moans from the newsysviews crew when
> the implications of this sink in ;-) ;-)

Oh no, not recursive function calls! :P

Actually, for the performance we're trying to obtain on the more
important views (ie tables, indexes), it might become an issue. It would
probably force us to C functions which we've thus-far avoided.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> On Wed, May 11, 2005 at 04:49:52PM -0400, Tom Lane wrote:
>> Besides, I can't wait to hear the moans from the newsysviews crew when
>> the implications of this sink in ;-) ;-)

> Oh no, not recursive function calls! :P

No, actually, I was wondering where the potentially N levels of schema
names would appear in the output ...

regards, tom lane

On Wed, May 11, 2005 at 05:28:22PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > On Wed, May 11, 2005 at 04:49:52PM -0400, Tom Lane wrote:
> >> Besides, I can't wait to hear the moans from the newsysviews crew when
> >> the implications of this sink in ;-) ;-)
>
> > Oh no, not recursive function calls! :P
>
> No, actually, I was wondering where the potentially N levels of schema
> names would appear in the output ...

My immediate thought is that they would be appended together in 'dot
notation'; 'schema1.schema2.schema3', since that's the definative way to
refer to the schema in such a scheme.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Adding to the ambiguity is the dot notation used for
composite columns. Don't forget the other end ignoring
those required parens.

is foo.bar.zap
a database.schema.table
a schema.table.column
a table.column.column

--elein

On Wed, May 11, 2005 at 03:21:42PM -0400, Tom Lane wrote:
> "Dave Held" <dave(dot)held(at)arraysg(dot)com> writes:
> > /*
> > * We check the catalog name and then ignore it.
> > */
> > if (!isValidNamespace(name[0]))
> > {
> > if (strcmp(name[0], get_database_name(MyDatabaseId)) != 0)
> > ereport(ERROR,
>
> Which more or less proves my point: the syntax is fundamentally
> ambiguous. I suppose people would learn not to use schema names that
> match the database they are in, but that doesn't make it a good idea to
> have sensible behavior depend on non-overlap of those names.
>
> [ thinks for awhile ... ]
>
> OTOH, what if we pretended that two-level-nested schemas ARE catalogs
> in the sense that the SQL spec expects? Then we could get rid of the
> pro-forma special case here, which isn't ever likely to do anything more
> useful than throw an error anyway. Thus, we'd go back to the pre-7.3
> notion that the current Postgres DB's name isn't part of the SQL naming
> scheme at all, and instead handle the spec's syntax requirements by
> setting up some conventions that make a schema act like what the spec
> says is a catalog.
>
> There are some nontrivial issues to be thought about here, like under
> what conditions "CREATE SCHEMA foo" ought to create a top-level schema
> versus creating a schema under some other schema that we are pretending
> is the active "catalog". But it seems on first glance like something
> could be worked out.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> On Wed, May 11, 2005 at 05:28:22PM -0400, Tom Lane wrote:
>> No, actually, I was wondering where the potentially N levels of schema
>> names would appear in the output ...

> My immediate thought is that they would be appended together in 'dot
> notation'; 'schema1.schema2.schema3', since that's the definative way to
> refer to the schema in such a scheme.

That's OK for human consumption but I'm not so sure it'll be of any
value to programs. At the very least you'd have to quotify the names,
so that a.b can be told from "a.b".

regards, tom lane

On Wed, May 11, 2005 at 05:43:32PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > On Wed, May 11, 2005 at 05:28:22PM -0400, Tom Lane wrote:
> >> No, actually, I was wondering where the potentially N levels of schema
> >> names would appear in the output ...
>
> > My immediate thought is that they would be appended together in 'dot
> > notation'; 'schema1.schema2.schema3', since that's the definative way to
> > refer to the schema in such a scheme.
>
> That's OK for human consumption but I'm not so sure it'll be of any
> value to programs. At the very least you'd have to quotify the names,
> so that a.b can be told from "a.b".

Very true. Ultimately the best way to handle this is probably to keep
the views basically as they are (meaning you would only show the
schema_name and oid of the schema that an object is in), and have a
function that will provide you a full schema path given a schema_oid.

On another note... is dbname.schema.table.column part of the standard?
It seems like if we're ever going to allow native cross-database
communication we'd want to preserve that. One thought is the use of a
leading . to indicate you're starting at the database level. No leading
. means you're in whatever database you're connected to. Another
possibility is that 'remote' databases (which might be on the same
server) get mapped into a fixed portion of the namespace hierarchy, such
as pg_rdb. I don't like cryptic names, but I certainly don't want to
type 'pg_remote_databas' everytime I refer to something remote.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Wed, May 11, 2005 at 02:41:43PM -0700, elein wrote:
> Adding to the ambiguity is the dot notation used for
> composite columns. Don't forget the other end ignoring
> those required parens.
>
> is foo.bar.zap
> a database.schema.table
> a schema.table.column
> a table.column.column

Wouldn't that be handled by the FROM clause having to identify only
tables and views? Is there anyplace where dot notation actually extends
from database name down to columns? If that's the case, it seems
reasonable to me to require the use of table aliases in cases where
there's ambiguity.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On 2005-05-11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> There are a number of issues that would have to be solved to make this
> actually work, but on first glance it seems like a possibly attractive
> idea.
>
> Besides, I can't wait to hear the moans from the newsysviews crew when
> the implications of this sink in ;-) ;-)

I'm not too worried; how many other things assume that schema.tablename
uniquely identifies a table? This is at least as large a change as
adding schemas in the first place.

Obvious strategies include:

- if only one additional nesting level is defined, add a "catalog"
column to match every "schema" column

- if multiple levels are defined, add a "schema_path" column with an
array of names to match every "schema" column.

If schema.tablename becomes non-unique (because this feature was
implemented _and_ someone creates the same schema in different catalogs)
then anything that currently queries the catalogs, whether directly or
via pg_tables (or even information_schema if you allow more than one
additional level) is going to have issues.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services