Re: [HACKERS] Oracle Style packages on postgres
Josh Berkus wrote: Bruce, Added to TODO: * Add the features of packages o Make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o Add session variables o Allow nested schemas Hmmm ... was there a reason we decided not to just make this explicitly tied to SQL2003 TYPES? I don't think anyone mentioned even knowing about TYPES. Do you have modifiations to this? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Oracle Style packages on postgres
Thanks for adding this Bruce! Is anyone going to be working on this immediately? If so, I'd be glad to work with someone. Unfortunately, I don't have the time to devote to taking something this big on, but I think it would be a really great thing to have. Just let me know [EMAIL PROTECTED] OR [EMAIL PROTECTED] Thanks! Bruce Momjian wrote: Added to TODO: * Add the features of packages o Make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o Add session variables o Allow nested schemas --- Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? --- Dave Held wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 8:43 AM To: Thomas Hallgren Cc: Tom Lane; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] I suppose. I think we should focus on the use cases for Oracle packages, rather than the specific functionality it provides. What things do people need PostgreSQL to do that it already doesn't do? Is that really the best way to go about things? Already RDBMSes are patchwork quilts of functionality. Is merely adding another patch the most elegant way to evolve the database? The problem is that Oracle et al are trying to be ORDBMSes and aren't exactly sure what the best way to go is. Instead of trying to formulate a rational plan for what an ORDBMS should even look like, they simply look at what would work with their existing infrastructure and tack on features. Then Postgres plays the copycat game. Instead of trying to play catch-up with Oracle, why not beat them at their own game? What packages provide is encapsulation. Hiding the data from the user and forcing him/her to use the public interface (methods). That is an important and admirable OO feature. Some people think that using the DB's security model can achieve the same thing. It can't, exactly, but there's an important lesson to be learned from the suggestion. The problem is that OOP is a *programming* paradigm, and a database is not a *programming language*. In a programming language, there really is no such thing as security. There is only visibility and accessibility. Private methods in an OOP language do not provide *security*; they only limit *accessibility*. Like so many other differences between the relational model and the OOP model, there is an impedance mismatch here. However, there is also opportunity. In an OOPL, you can say: Users can call this method from here, but not from there. What you *can't* say is: User X can call this method, but User Y cannot. As you can see, these are orthogonal concepts. You could call the first accessibility by location and the second accessibility by authentication. An ORDBMS should support both. Private does not respect your identity, only your calling location. An ACL does not respect your calling scope, only your identity. A system that has both is clearly more flexible than one that only has one or the other. Now what you need to keep in mind is that each visibility model serves a different purpose. The purpose of a security model is to limit *who* can see/touch certain data because the data has intrinsic value. The purpose of an accessibility model is to limit *where* and *how* data can be seen/touched in order to preserve *program invariants*. So if you have an object (or tuple!) that records the start and stop time of some process, it is probably a logical invariant that the stop time is greater than or equal to the start time. For this reason, in a PL, you would encapsulate these fields (attributes) and only provide controlled access to update them that checks and preserves the invariant, *no matter who you are*. You don't want a superuser violating this invariant any more than Sue User. Now you might object that constraints allow you to preserve invariants as well, and indeed they do. But constraints do not respect calling scope. Suppose there is a process that needs to update the timestamps in a way that temporarily breaks the invariant but restores it afterwards. The only way to effect this in a constraint environment is to drop the constraint, perform the operation, and restore it. However, dropping a constraint is not an ideal solution because there may be other unprivileged processes operating on the relation that still need the constraint to be enforced. There is no way to say: There is a priviledged class of methods
Re: [HACKERS] Oracle Style packages on postgres
* Jonah H. Harris ([EMAIL PROTECTED]) wrote: Is anyone going to be working on this immediately? If so, I'd be glad to work with someone. Unfortunately, I don't have the time to devote to taking something this big on, but I think it would be a really great thing to have. Just let me know [EMAIL PROTECTED] OR [EMAIL PROTECTED] Thanks! It strikes me as slightly unlikely that anyone will start working on this immediately, but I can tell you it's something that some of my users have been asking for and so once I finish off my current work on roles I'll probably be interested in working on this. Stephen Bruce Momjian wrote: Added to TODO: * Add the features of packages o Make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o Add session variables o Allow nested schemas --- Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? --- Dave Held wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 8:43 AM To: Thomas Hallgren Cc: Tom Lane; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] I suppose. I think we should focus on the use cases for Oracle packages, rather than the specific functionality it provides. What things do people need PostgreSQL to do that it already doesn't do? Is that really the best way to go about things? Already RDBMSes are patchwork quilts of functionality. Is merely adding another patch the most elegant way to evolve the database? The problem is that Oracle et al are trying to be ORDBMSes and aren't exactly sure what the best way to go is. Instead of trying to formulate a rational plan for what an ORDBMS should even look like, they simply look at what would work with their existing infrastructure and tack on features. Then Postgres plays the copycat game. Instead of trying to play catch-up with Oracle, why not beat them at their own game? What packages provide is encapsulation. Hiding the data from the user and forcing him/her to use the public interface (methods). That is an important and admirable OO feature. Some people think that using the DB's security model can achieve the same thing. It can't, exactly, but there's an important lesson to be learned from the suggestion. The problem is that OOP is a *programming* paradigm, and a database is not a *programming language*. In a programming language, there really is no such thing as security. There is only visibility and accessibility. Private methods in an OOP language do not provide *security*; they only limit *accessibility*. Like so many other differences between the relational model and the OOP model, there is an impedance mismatch here. However, there is also opportunity. In an OOPL, you can say: Users can call this method from here, but not from there. What you *can't* say is: User X can call this method, but User Y cannot. As you can see, these are orthogonal concepts. You could call the first accessibility by location and the second accessibility by authentication. An ORDBMS should support both. Private does not respect your identity, only your calling location. An ACL does not respect your calling scope, only your identity. A system that has both is clearly more flexible than one that only has one or the other. Now what you need to keep in mind is that each visibility model serves a different purpose. The purpose of a security model is to limit *who* can see/touch certain data because the data has intrinsic value. The purpose of an accessibility model is to limit *where* and *how* data can be seen/touched in order to preserve *program invariants*. So if you have an object (or tuple!) that records the start and stop time of some process, it is probably a logical invariant that the stop time is greater than or equal to the start time. For this reason, in a PL, you would encapsulate these fields (attributes) and only provide controlled access to update them that checks and preserves the invariant, *no matter who you are*. You don't want a superuser violating this invariant any more than Sue User. Now you might object that constraints allow you to preserve invariants as well, and indeed they do. But constraints do not respect calling scope. Suppose there is a process that needs to update the timestamps in a way that temporarily breaks the invariant but restores it afterwards
Re: [HACKERS] Oracle Style packages on postgres
Bruce, Added to TODO: * Add the features of packages o Make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o Add session variables o Allow nested schemas Hmmm ... was there a reason we decided not to just make this explicitly tied to SQL2003 TYPES? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle Style packages on postgres
Added to TODO: * Add the features of packages o Make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o Add session variables o Allow nested schemas --- Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? --- Dave Held wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 8:43 AM To: Thomas Hallgren Cc: Tom Lane; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] I suppose. I think we should focus on the use cases for Oracle packages, rather than the specific functionality it provides. What things do people need PostgreSQL to do that it already doesn't do? Is that really the best way to go about things? Already RDBMSes are patchwork quilts of functionality. Is merely adding another patch the most elegant way to evolve the database? The problem is that Oracle et al are trying to be ORDBMSes and aren't exactly sure what the best way to go is. Instead of trying to formulate a rational plan for what an ORDBMS should even look like, they simply look at what would work with their existing infrastructure and tack on features. Then Postgres plays the copycat game. Instead of trying to play catch-up with Oracle, why not beat them at their own game? What packages provide is encapsulation. Hiding the data from the user and forcing him/her to use the public interface (methods). That is an important and admirable OO feature. Some people think that using the DB's security model can achieve the same thing. It can't, exactly, but there's an important lesson to be learned from the suggestion. The problem is that OOP is a *programming* paradigm, and a database is not a *programming language*. In a programming language, there really is no such thing as security. There is only visibility and accessibility. Private methods in an OOP language do not provide *security*; they only limit *accessibility*. Like so many other differences between the relational model and the OOP model, there is an impedance mismatch here. However, there is also opportunity. In an OOPL, you can say: Users can call this method from here, but not from there. What you *can't* say is: User X can call this method, but User Y cannot. As you can see, these are orthogonal concepts. You could call the first accessibility by location and the second accessibility by authentication. An ORDBMS should support both. Private does not respect your identity, only your calling location. An ACL does not respect your calling scope, only your identity. A system that has both is clearly more flexible than one that only has one or the other. Now what you need to keep in mind is that each visibility model serves a different purpose. The purpose of a security model is to limit *who* can see/touch certain data because the data has intrinsic value. The purpose of an accessibility model is to limit *where* and *how* data can be seen/touched in order to preserve *program invariants*. So if you have an object (or tuple!) that records the start and stop time of some process, it is probably a logical invariant that the stop time is greater than or equal to the start time. For this reason, in a PL, you would encapsulate these fields (attributes) and only provide controlled access to update them that checks and preserves the invariant, *no matter who you are*. You don't want a superuser violating this invariant any more than Sue User. Now you might object that constraints allow you to preserve invariants as well, and indeed they do. But constraints do not respect calling scope. Suppose there is a process that needs to update the timestamps in a way that temporarily breaks the invariant but restores it afterwards. The only way to effect this in a constraint environment is to drop the constraint, perform the operation, and restore it. However, dropping a constraint is not an ideal solution because there may be other unprivileged processes operating on the relation that still need the constraint to be enforced. There is no way to say: There is a priviledged class of methods that is allowed to violate this constraint because they are trusted to restore it upon completion. Note that this is different from saying There is a priviledged class of users that is allowed to violate
Re: [HACKERS] Oracle Style packages on postgres
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 11, 2005 2:22 PM
To: Dave Held
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Oracle Style packages on postgres
Dave Held [EMAIL PROTECTED] 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.
Not at all. Ambiguity means that there are two equally valid
parses. Under the semantics I proposed, schema names take
precedence. That is, given:
db: foo
schema: bar
schema: foo.bar
The expression foo.bar.rel.col refers to schema foo.bar, and not
to db foo, schema bar. If by fundamentally ambiguous, you mean
there is no a priori reason to choose one set of semantics over
another, I would tend to disagree, but the syntax as I proposed
it is not ambiguous. We use precedence to eliminate otherwise
valid parses all the time.
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.
There's nothing wrong with using a schema name that matches the
db. The only confusion comes when you put nested elements at
both the db level and schema level having the same names. Since
I presume most people don't specify db names in their queries,
having schemas take precedence makes the most sense to me.
[ 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.
[...]
I think this would be worse than not having nested schemas at all.
It looks, feels, and smells like a hack. I think there should be
a reasonable depth to schema nesting, but I think it should be
much larger than 2. I think 8 is much more reasonable. One can
argue that nested schemas are nothing more than syntactic sugar,
and this is most definitely true. But as programming language
design teaches us, syntactic sugar is everything. The better our
tools can model our problem spaces, the better they can help us
solve our problems.
A way in which nested schemas are more than syntactic sugar is in
the fact that they can provide a convenient means of additinoal
security management. Rather than twiddling with the privileges on
groups of objects within a schema, objects that should have similar
privileges can be put in the same subschema.
However, returning to the original topic of the thread, nested
schemas are not nearly as interesting to me as the encapsulation
provided by a package-like feature. To be honest, though, what
tantalizes me is not the prospect of a package feature but an
expansion of the Type system.
As a reasonably popular production system, Postgres must necessarily
be conservative. But its roots lay in experimentation, and vestiges
of those roots can still be seen in its structure. Because of its
maturity, Postgres is well positioned to implement some rather
advanced concepts, but perhaps the most radical of them should be
implemented in a fork rather than the main system.
Traditionally, a database is seen as a warehouse of raw data.
ODBMSes position themselves as the next generation by viewing a
database as a collection of persistent, richly structured objects.
Both views have strengths and weaknesses. Postgres takes an
interesting middle ground position within the ORDBMS space. It
is heavily relational with strong support for standard SQL and
numerous query tuning options. But it also features an interesting
number of rather non-relational concepts, like custom operator
definitions, operator classes, user-defined conversions and types.
However, it seems to me that these features are probably very
underutilized.
This is probably due to two reasons: 1) most programmers aren't used
to being able to define custom operators in their favorite programming
language, so the concept isn't familiar enough to them to try it in
their DBMS. 2) The other features which support this aren't designed
or presented in a cohesive manner that impresses the programmer that
this is a compelling and superior way to go about things.
The fact is, operator overloading is a *very* powerful way to
program. In particular, it is one of the key factors in supporting
generic programming in a natural way. People who are unsure
Re: [HACKERS] Oracle Style packages on postgres
On 2005-05-11, Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Oracle Style packages on postgres
David Fetter wrote: On Tue, May 10, 2005 at 09:49:13PM -0400, Bruce Momjian wrote: David Fetter wrote: On Tue, May 10, 2005 at 06:55:39PM -0400, Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? Well, some kind of nestable namespace for objects, anyhow. How would nested namespaces be different from nested schemas? I thought the two were the same. I was thinking of nested namespaces in the more limited sense of namespaces for bundles of functions/stored procedures rather than a full-on hierarchy where a table can have a schema which resides inside another schema which resides...unless people really want to have it that way. Oh, so allow only functions to sit in the sub-namespace? Yea, we could do that, but it seems sort of limiting. However, I am unclear how we would do sub-namespaces either. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Oracle Style packages on postgres
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 11:42 PM To: Bruce Momjian Cc: Dave Held; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] There's been a lot of handwaving about nested schemas in this thread, but no one has explained how they could actually *work* given the SQL syntax rules. In general, a is a column from the current table set, a.b is a column b in table/alias a from the current query, a.b.c is a column c from table b in schema a, a.b.c.d is a column d from table c in schema b in catalog a, and any more than that is a syntax error. I do not see how to add nested schemas without creating unworkable ambiguities, not to say outright violations of the spec. Clearly nested schemas would violate the SQL spec, as do the numerous missing features in Postgres. Obviously, they would have to be a sort of non-conforming extension. It's an opportunity for Postgres to take the lead and influence the next standard, I guess. Unless the community decides that it's not worth the hassle, which seems much more likely. I am curious to know what the unworkable ambiguities are. I propose that if there is any ambiguity at all, just fail the parse and leave it to the user to write something sensible. Otherwise, it's just a matter of defining a precise precedence for resolving name scopes, which doesn't seem very tricky at all. That is, if a.b is the name of a schema b nested within a schema a, then a.b.c.d refers to a column d of table c in schema b in schema a. If a is not the name of a schema, then check to see if it's the name of a database. If it is, then a.b.c.d has the meaning you define above. If it's not, then it's an error. 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. For the parts after the first and before the last two, just search the appropriate schemas. As far as I can tell, this syntax is completely backwards-compatible with existing SQL syntax. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Oracle Style packages on postgres
Dave Held [EMAIL PROTECTED] 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(). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Oracle Style packages on postgres
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 11, 2005 10:55 AM
To: Dave Held
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Oracle Style packages on postgres
Dave Held [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle Style packages on postgres
Dave Held [EMAIL PROTECTED] 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
Re: [HACKERS] Oracle Style packages on postgres
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); -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Oracle Style packages on postgres
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); -- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Oracle Style packages on postgres
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Oracle Style packages on postgres
Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] 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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Oracle Style packages on postgres
Bruce Momjian pgman@candle.pha.pa.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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle Style packages on postgres
On Wed, May 11, 2005 at 04:49:52PM -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.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 [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Oracle Style packages on postgres
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
On Wed, May 11, 2005 at 05:28:22PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle Style packages on postgres
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 [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle Style packages on postgres
On Wed, May 11, 2005 at 05:43:32PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Oracle Style packages on postgres
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 [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle Style packages on postgres
Bruce Momjian wrote: Josh Berkus wrote: I think that private variables and private functions need to be part of the definition. OK, so it seems we need: C static/private functions for schemas C static/private variables for schemas Are private variables implemented via the temporary per-session schema? nested schemas What does the standard say? The standard says that rather then using nested schemas or packages in conjunction with functions in order to group functions with data, use user defined types with attributes and methods. Methods can be STATIC, INSTANCE or CONSTRUCTOR. AFAICS there's nothing in the standard that alters the visibility of methods and attributes, so private is not covered (and don't recall that Oracle has that kind of visibility control either). Normal access restrictions apply of course. I can't find any mention of schema variables. I think all life-cycle management of data is reduced to table storage. And why not? A temporary table can be viewed as session data right? Using a KISS approach, the easiest thing to do that also would bring us closer to the standard, is to extend the notion of user defined types to include methods and conclude that storing session data in other ways than using temporary tables should be PL specific. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle Style packages on postgres
-- Forwarded message -- From: Adrian Maier [EMAIL PROTECTED] Date: May 10, 2005 12:01 PM Subject: Re: [HACKERS] Oracle Style packages on postgres To: Jim C. Nasby [EMAIL PROTECTED] On 5/9/05, Jim C. Nasby [EMAIL PROTECTED] wrote: On Sun, May 08, 2005 at 10:38:41PM -0500, Bob wrote: One simple benefit to packages is just organization of related code. Which, IMHO, is greatly diminished by the lack of schema.package.function notation. BTW, the original post referred to this as user.package.function, but I believe that technically it's actually schema.package.function (Oracle tends to mix schemas and users). In any case, schema.package.function is what would make sense in PostgreSQL. Personally, I think the biggest win here would be adding package support and syntax to plpgsql. Not only would it make porting from Oracle easier, it would also make plpgsql much, much more powerful. Hello, What do you think about having some kind of language-independent packages ? I'm thinking that it could be handy to implement some functions in plpgsql, some functions in plpython and so . And then bundle them together into the same package. Cheers, Adrian Maier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
Thomas Hallgren wrote: Bruce Momjian wrote: Josh Berkus wrote: I think that private variables and private functions need to be part of the definition. OK, so it seems we need: C static/private functions for schemas C static/private variables for schemas Are private variables implemented via the temporary per-session schema? nested schemas What does the standard say? The standard says that rather then using nested schemas or packages in conjunction with functions in order to group functions with data, use user defined types with attributes and methods. Methods can be STATIC, INSTANCE or CONSTRUCTOR. So it sounds like you group the functions into user-defined types, rather than nested schemas. So you assocate functions with a table? AFAICS there's nothing in the standard that alters the visibility of methods and attributes, so private is not covered (and don't recall that Oracle has that kind of visibility control either). Normal access restrictions apply of course. I can't find any mention of schema variables. I think all life-cycle management of data is reduced to table storage. And why not? A temporary table can be viewed as session data right? Using a KISS approach, the easiest thing to do that also would bring us closer to the standard, is to extend the notion of user defined types to include methods and conclude that storing session data in other ways than using temporary tables should be PL specific. I suppose. I think we should focus on the use cases for Oracle packages, rather than the specific functionality it provides. What things do people need PostgreSQL to do that it already doesn't do? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Oracle Style packages on postgres
Bruce Momjian wrote: Thomas Hallgren wrote: Bruce Momjian wrote: Josh Berkus wrote: I think that private variables and private functions need to be part of the definition. OK, so it seems we need: C static/private functions for schemas C static/private variables for schemas Are private variables implemented via the temporary per-session schema? nested schemas What does the standard say? The standard says that rather then using nested schemas or packages in conjunction with functions in order to group functions with data, use user defined types with attributes and methods. Methods can be STATIC, INSTANCE or CONSTRUCTOR. So it sounds like you group the functions into user-defined types, rather than nested schemas. Yes, you'd get schema.type.method as the fully qualified name. So you assocate functions with a table? Not necessarily a table. A type is just a type. A type containing just static methods is not very different from an Oracle package. A table created from a type may of course have methods associated with it. That gets really interesting when you use INSTANCE methods. They act on a per row basis so that you can do things like: SELECT x.someMethod() FROM someTable x; rather than as today. SELECT someFunction(x) FROM someTable x; Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle Style packages on postgres
Bruce Momjian schrieb: OK, so it seems we need: C static/private functions for schemas C static/private variables for schemas Are private variables implemented via the temporary per-session schema? nested schemas What does the standard say? Is that it? Yeah, that would be great. And don't forget global variables for pl/pgsql. Daniel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle Style packages on postgres
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 8:43 AM To: Thomas Hallgren Cc: Tom Lane; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] I suppose. I think we should focus on the use cases for Oracle packages, rather than the specific functionality it provides. What things do people need PostgreSQL to do that it already doesn't do? Is that really the best way to go about things? Already RDBMSes are patchwork quilts of functionality. Is merely adding another patch the most elegant way to evolve the database? The problem is that Oracle et al are trying to be ORDBMSes and aren't exactly sure what the best way to go is. Instead of trying to formulate a rational plan for what an ORDBMS should even look like, they simply look at what would work with their existing infrastructure and tack on features. Then Postgres plays the copycat game. Instead of trying to play catch-up with Oracle, why not beat them at their own game? What packages provide is encapsulation. Hiding the data from the user and forcing him/her to use the public interface (methods). That is an important and admirable OO feature. Some people think that using the DB's security model can achieve the same thing. It can't, exactly, but there's an important lesson to be learned from the suggestion. The problem is that OOP is a *programming* paradigm, and a database is not a *programming language*. In a programming language, there really is no such thing as security. There is only visibility and accessibility. Private methods in an OOP language do not provide *security*; they only limit *accessibility*. Like so many other differences between the relational model and the OOP model, there is an impedance mismatch here. However, there is also opportunity. In an OOPL, you can say: Users can call this method from here, but not from there. What you *can't* say is: User X can call this method, but User Y cannot. As you can see, these are orthogonal concepts. You could call the first accessibility by location and the second accessibility by authentication. An ORDBMS should support both. Private does not respect your identity, only your calling location. An ACL does not respect your calling scope, only your identity. A system that has both is clearly more flexible than one that only has one or the other. Now what you need to keep in mind is that each visibility model serves a different purpose. The purpose of a security model is to limit *who* can see/touch certain data because the data has intrinsic value. The purpose of an accessibility model is to limit *where* and *how* data can be seen/touched in order to preserve *program invariants*. So if you have an object (or tuple!) that records the start and stop time of some process, it is probably a logical invariant that the stop time is greater than or equal to the start time. For this reason, in a PL, you would encapsulate these fields (attributes) and only provide controlled access to update them that checks and preserves the invariant, *no matter who you are*. You don't want a superuser violating this invariant any more than Sue User. Now you might object that constraints allow you to preserve invariants as well, and indeed they do. But constraints do not respect calling scope. Suppose there is a process that needs to update the timestamps in a way that temporarily breaks the invariant but restores it afterwards. The only way to effect this in a constraint environment is to drop the constraint, perform the operation, and restore it. However, dropping a constraint is not an ideal solution because there may be other unprivileged processes operating on the relation that still need the constraint to be enforced. There is no way to say: There is a priviledged class of methods that is allowed to violate this constraint because they are trusted to restore it upon completion. Note that this is different from saying There is a priviledged class of users that is allowed to violate this constraint. If you try to do something like give read-only access to everybody and only write access to one user and define that user to be the owner of the methods that update the data, you have to follow the convention that that user only operates through the defined interface, and doesn't hack the data directly. That's because user-level accessibility is not the same as scope- level accessibility. Whereas, if you define something like a package, and say: Package X is allowed full and complete access to relation Y, and stick the interface methods in X, you still have all the user-level security you want while preserving the invariants in the most elegant way. So you can think of a package as a scope in a programming language. It's like a user, but it is not a user. A user has privileges that cut across scopes. Now, whether packages should
Re: [HACKERS] Oracle Style packages on postgres
On Tue, May 10, 2005 at 12:01:54PM +0300, Adrian Maier wrote: Personally, I think the biggest win here would be adding package support and syntax to plpgsql. Not only would it make porting from Oracle easier, it would also make plpgsql much, much more powerful. Hello, What do you think about having some kind of language-independent packages ? I'm thinking that it could be handy to implement some functions in plpgsql, some functions in plpython and so . And then bundle them together into the same package. Personally, I basically only use plpgsql, but I can certainly see where there would be value in being able to include functions and procedures from multiple languages in one package. But I suspect this will also make some things more difficult, such as global static variables. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Oracle Style packages on postgres
On Mon, May 09, 2005 at 11:24:45PM +0200, Thomas Hallgren wrote: In Oracle you can use the syntax: schema.package.function() but you can just as well use the syntax: schema.type.static method() Why do you need both? If PostgreSQL is going to add new nice features that enables better namespace handling and global variables, take a look at Oracles UDT's with static and instance methods. Only thing that I'm not sure is there is static variables. If it's missing, we could add that easilly and give them the same life-span as the session. It's been a while since I used types, but here's some issues I can think of: I don't believe types allow for internal-only methods. I seem to recall other limitations on what types could do as opposed to packages. Of course, we need not restrict ourselves in such a manner. Types are not used nearly as much as packages (this is an issue if we care about enabling Oracle users to migrate). Types generally force you to use them in relation to some database object. Packages have no such restriction. Don't get me wrong, I think supporting more powerful types would be a welcome addition, but I don't think they can be as flexable as packages. The good news is that they should both be able to use the same underlying framework. Types are afterall just a specialized implementation of packages. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Oracle Style packages on postgres
On Mon, May 09, 2005 at 09:56:53PM -0400, Bruce Momjian wrote: OK, so it seems we need: C static/private functions for schemas C static/private variables for schemas Are private variables implemented via the temporary per-session schema? nested schemas What does the standard say? Is that it? I think a big part of the usefulness of packages is in supplying an additional level of grouping common things together. Of course, nested schemas with public/private functions (and procedures, lest we forget them) is a much better way to do this, since a schema can encompass everything you'd need; tables, views, types, etc. Having said that, I would say that private variables need to be exposed via the same nested schema interface as everything else. If the implementation under the covers is via the temporary schema, that's fine. As for using temporary tables as session storage, that has a huge performance penalty associated with it. Part of the advantage to package variables is that you can use them to cache information your code will need to access frequently. That access then becomes a simple variable or array read, which is obviously much faster than parsing a query to hit a temp table. There is one feature not mentioned by Bruce's design, and that's initialization (and teardown) code. I don't recall using that capability in Oracle, but I was wondering if others with more experience could comment on it. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Oracle Style packages on postgres
Jim C. Nasby wrote: I don't believe types allow for internal-only methods. I seem to recall other limitations on what types could do as opposed to packages. Of course, we need not restrict ourselves in such a manner. Do Oracle packages support internal only functions? If they do, then I agree, that's a feature that the SQL standard doesn't have. Types are not used nearly as much as packages (this is an issue if we care about enabling Oracle users to migrate). Types generally force you to use them in relation to some database object. Packages have no such restriction. If used as a package, i.e. only containing static methods, you don't need to use the type in relation to anything. It's simply a namespace. If used with a temporary table, you get a very neat, standardized, cross-language way of managing session data. Don't get me wrong, I think supporting more powerful types would be a welcome addition, but I don't think they can be as flexable as packages. I see this differently. A full implementation of the SQL-standard for UDT's will allow abstract types, inheritance, method overloading, etc. It quickly becomes far more flexible then Oracle packages. A full implementation is of course beyond the scope for what's needed to accommodate the needs of those who use packages but a simple implementation is extendable within the scope of the standard. The good news is that they should both be able to use the same underlying framework. Types are afterall just a specialized implementation of packages. Right. Given a good implementation of types, packages would be easy to implement. The other way around would not be possible. A package is a very restricted type that contains static methods only. Possibly with the extension of some kind of method/attribute visibility. So do we need internal only functions although they are not covered by the SQL-standard? If the answer is no, then IMO we should follow the standard and use types, not packages. If the answer is yes, then the SQL-standard is not enough. Should we then use packages or simply introduce the keyword PRIVATE on methods of a type? Personally, I'd go for the latter and then, if necessary, build packages on top of that in for the benefit of Oracle users who wants to migrate. A fully fledged type system will ease Oracle migration too since Oracle already has this. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Oracle Style packages on postgres
On Tue, May 10, 2005 at 08:40:16PM +0200, Thomas Hallgren wrote: Jim C. Nasby wrote: I don't believe types allow for internal-only methods. I seem to recall other limitations on what types could do as opposed to packages. Of course, we need not restrict ourselves in such a manner. Do Oracle packages support internal only functions? If they do, then I agree, that's a feature that the SQL standard doesn't have. AFAIK anything in an Oracle packages can be internal-only. You just exclude it from the header. I see this differently. A full implementation of the SQL-standard for UDT's will allow abstract types, inheritance, method overloading, etc. It quickly becomes far more flexible then Oracle packages. A full implementation is of course beyond the scope for what's needed to accommodate the needs of those who use packages but a simple implementation is extendable within the scope of the standard. Packages allow for abstract types and method overloading. They don't allow for inheritance. The good news is that they should both be able to use the same underlying framework. Types are afterall just a specialized implementation of packages. Right. Given a good implementation of types, packages would be easy to implement. The other way around would not be possible. A package is a very restricted type that contains static methods only. Possibly with the extension of some kind of method/attribute visibility. I guess maybe I'm not clear on what you mean by static methods. IIRC, in Oracle nomenclature, static means it will retain state between invocations in the same session. Of course, functions and procedures that don't do this are also allowed. Basically, before we assume that one implementation allows for the other I think some research needs to be done. Hopefully someone on the list is familiar with both. I think it would be a huge win if we could offer a compatability mechanism that makes it easy for Oracle packages to be used in PostgreSQL, making migration from Oracle much, much easier. So do we need internal only functions although they are not covered by the SQL-standard? If the answer is no, then IMO we should follow the standard and use types, not packages. If the answer is yes, then the SQL-standard is not enough. Should we then use packages or simply introduce the keyword PRIVATE on methods of a type? Personally, I'd go for the latter and then, if necessary, build packages on top of that in for the benefit of Oracle users who wants to migrate. A fully fledged type system will ease Oracle migration too since Oracle already has this. I think both should allow for private functions/procedures/methods. BTW, I'm also very keen on the idea of nested schemas, which is another possible means to the package ends. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle Style packages on postgres
Jim C. Nasby wrote: I guess maybe I'm not clear on what you mean by static methods. IIRC, in Oracle nomenclature, static means it will retain state between invocations in the same session. Of course, functions and procedures that don't do this are also allowed. A STATIC prefix on a method simply means that it is not tied to a particular instance of the type where it is defined. You have the type Foo with the method bar(). If the method is STATIC, you can use: SELECT Foo.bar(); If it's an INSTANCE method, you can only call it when you have an instance available, so if FooTable is a table described by the type Foo and bar is non-static, you could write: SELECT x.bar() FROM FooTable x; I think both should allow for private functions/procedures/methods. BTW, I'm also very keen on the idea of nested schemas, which is another possible means to the package ends. I'd like that too although I don't think it's included in the SQL-standard. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle Style packages on postgres
Dave Held wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 8:43 AM To: Thomas Hallgren Cc: Tom Lane; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] I suppose. I think we should focus on the use cases for Oracle packages, rather than the specific functionality it provides. What things do people need PostgreSQL to do that it already doesn't do? Is that really the best way to go about things? Already RDBMSes are patchwork quilts of functionality. Is merely adding another patch the most elegant way to evolve the database? The problem is that Oracle et al are trying to be ORDBMSes and aren't exactly sure what the best way to go is. Instead of trying to formulate a rational plan for what an ORDBMS should even look like, they simply look at what would work with their existing infrastructure and tack on features. Then Postgres plays the copycat game. Instead of trying to play catch-up with Oracle, why not beat them at their own game? I was unclear. I was suggesting exactly what you posted, that we look at what functionality we _need_ from Oracle packages, rather than the functionality of Oracle packages themselves. My assumption is that Oracle does some things we need, and some things we don't, and does them in some ways we will like, and others we will not, so let's look at the actuall use cases that we need to address. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Oracle Style packages on postgres
OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? --- Dave Held wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 10, 2005 8:43 AM To: Thomas Hallgren Cc: Tom Lane; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Oracle Style packages on postgres [...] I suppose. I think we should focus on the use cases for Oracle packages, rather than the specific functionality it provides. What things do people need PostgreSQL to do that it already doesn't do? Is that really the best way to go about things? Already RDBMSes are patchwork quilts of functionality. Is merely adding another patch the most elegant way to evolve the database? The problem is that Oracle et al are trying to be ORDBMSes and aren't exactly sure what the best way to go is. Instead of trying to formulate a rational plan for what an ORDBMS should even look like, they simply look at what would work with their existing infrastructure and tack on features. Then Postgres plays the copycat game. Instead of trying to play catch-up with Oracle, why not beat them at their own game? What packages provide is encapsulation. Hiding the data from the user and forcing him/her to use the public interface (methods). That is an important and admirable OO feature. Some people think that using the DB's security model can achieve the same thing. It can't, exactly, but there's an important lesson to be learned from the suggestion. The problem is that OOP is a *programming* paradigm, and a database is not a *programming language*. In a programming language, there really is no such thing as security. There is only visibility and accessibility. Private methods in an OOP language do not provide *security*; they only limit *accessibility*. Like so many other differences between the relational model and the OOP model, there is an impedance mismatch here. However, there is also opportunity. In an OOPL, you can say: Users can call this method from here, but not from there. What you *can't* say is: User X can call this method, but User Y cannot. As you can see, these are orthogonal concepts. You could call the first accessibility by location and the second accessibility by authentication. An ORDBMS should support both. Private does not respect your identity, only your calling location. An ACL does not respect your calling scope, only your identity. A system that has both is clearly more flexible than one that only has one or the other. Now what you need to keep in mind is that each visibility model serves a different purpose. The purpose of a security model is to limit *who* can see/touch certain data because the data has intrinsic value. The purpose of an accessibility model is to limit *where* and *how* data can be seen/touched in order to preserve *program invariants*. So if you have an object (or tuple!) that records the start and stop time of some process, it is probably a logical invariant that the stop time is greater than or equal to the start time. For this reason, in a PL, you would encapsulate these fields (attributes) and only provide controlled access to update them that checks and preserves the invariant, *no matter who you are*. You don't want a superuser violating this invariant any more than Sue User. Now you might object that constraints allow you to preserve invariants as well, and indeed they do. But constraints do not respect calling scope. Suppose there is a process that needs to update the timestamps in a way that temporarily breaks the invariant but restores it afterwards. The only way to effect this in a constraint environment is to drop the constraint, perform the operation, and restore it. However, dropping a constraint is not an ideal solution because there may be other unprivileged processes operating on the relation that still need the constraint to be enforced. There is no way to say: There is a priviledged class of methods that is allowed to violate this constraint because they are trusted to restore it upon completion. Note that this is different from saying There is a priviledged class of users that is allowed to violate this constraint. If you try to do something like give read-only access to everybody and only write access to one user and define that user to be the owner of the methods that update the data, you have to follow the convention that that user only operates through the defined interface, and doesn't hack the data directly. That's because user-level accessibility is not the same as scope- level accessibility. Whereas, if you define
Re: [HACKERS] Oracle Style packages on postgres
On Tue, May 10, 2005 at 06:55:39PM -0400, Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? Well, some kind of nestable namespace for objects, anyhow. I'll look over the SQL:2003 draft and see if I can find anything along that line in there. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Oracle Style packages on postgres
David Fetter wrote: On Tue, May 10, 2005 at 06:55:39PM -0400, Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? Well, some kind of nestable namespace for objects, anyhow. How would nested namespaces be different from nested schemas? I thought the two were the same. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
On Tue, May 10, 2005 at 09:49:13PM -0400, Bruce Momjian wrote: David Fetter wrote: On Tue, May 10, 2005 at 06:55:39PM -0400, Bruce Momjian wrote: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? Well, some kind of nestable namespace for objects, anyhow. How would nested namespaces be different from nested schemas? I thought the two were the same. I was thinking of nested namespaces in the more limited sense of namespaces for bundles of functions/stored procedures rather than a full-on hierarchy where a table can have a schema which resides inside another schema which resides...unless people really want to have it that way. In a slightly related situation, at least in my mind, it seems like for full-on ORDBMS functionality, it should be possible to have a column of type schema or setof record, c., and be able to take these things apart at each row. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Oracle Style packages on postgres
Bruce Momjian pgman@candle.pha.pa.us writes: OK, so it seems we need: o make private objects accessable only to objects in the same schema o Allow current_schema.objname to access current schema objects o session variables o nested schemas? There's been a lot of handwaving about nested schemas in this thread, but no one has explained how they could actually *work* given the SQL syntax rules. In general, a is a column from the current table set, a.b is a column b in table/alias a from the current query, a.b.c is a column c from table b in schema a, a.b.c.d is a column d from table c in schema b in catalog a, and any more than that is a syntax error. I do not see how to add nested schemas without creating unworkable ambiguities, not to say outright violations of the spec. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
On E, 2005-05-09 at 07:36 +0200, Thomas Hallgren wrote: Satoshi Nagayasu wrote: An oracle package is created when first referenced. Its initialization code is run once (ie costly queries to populate session wide package params) and the package dies at the end of the session An analogy with OOP is that it's like having a single class instance available for the duration of a session. PL/Java has an object called Session that does exactly this. And pl/python has a global dictionary SD for the same purpose. It is not available from other languages at present. Are Packages supposed to be cross-language? Probably not, as they already have most of the needed features. Maybe we can set up some lighter version of package for cross-language features (like installing removing a group of functions) but this are much less needed for more advanced languages. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Oracle Style packages on postgres
On Sun, May 08, 2005 at 10:38:41PM -0500, Bob wrote: One simple benefit to packages is just organization of related code. Which, IMHO, is greatly diminished by the lack of schema.package.function notation. BTW, the original post referred to this as user.package.function, but I believe that technically it's actually schema.package.function (Oracle tends to mix schemas and users). In any case, schema.package.function is what would make sense in PostgreSQL. Personally, I think the biggest win here would be adding package support and syntax to plpgsql. Not only would it make porting from Oracle easier, it would also make plpgsql much, much more powerful. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Oracle Style packages on postgres
Rmm,
A black box processing engine with one or more public access functions
that retains state across calls
In other words, an Object. grin
Oracle style package creation syntax is split into header and body so that
the body(code) can be re-compiled without invalidating dependent objects.
Postgres syntax for the dbms_output example (in any postgres server side
language) would be along the lines of:
CREATE OR REPLACE PACKAGE HEADER dbms_output AS
FUNCTION dbms_output_put_line(text) RETURNS text,
FUNCTION dbms_output_get_lines() RETURNS text;
CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
language;
Hmmm. What about package variables? For me, this is one of the most
valuable parts of packages.
I've also never much liked Oracle's seperate package_header and package_body
declaration structure: if the two are intrinsically tied, why not make it one
declaration? Is syntactical compatibility important enough that we need to
imitate their design errors?
Adding pg_package with a link from pg_proc are the only changes required
to the data dictionary.
It would be nice to have similar dotted syntax as oracle
(user.package.function) but would this mess up postgres namespaces?
Yes, actually. If you look at the discussion, this is what killed the 2001
proposal; packages were proposed as orthagonal to schema which was not
acceptable.
However, now that schema are well established, it seems like this namespace
issue is limited. The problem would be that you'd have to make sure that no
two schema and packages had the same name, or that there would be an
automatic precedence of shema, package established.
So, given a shema named dataloader and a package named dataloader and a
function named copy_it(filename), what would happen is:
dataloader.dataloader.copy_it('/tmp/somefile')
... would be absolutely clear
dataloader.copy_it('/tmp/somefile')
... would attempt to call the copy_it function in the dataloader
*schema*, not the dataloader *package*.
The above seems inevitable, and not really a problem to me. We simply warn
people in the docs of the behavior, and to avoid duplicate naming.
I think there are more important questions:
1) how do you prevent users from executing the package functions outside of
the package?
2) Have you taken care of package variables? If so, are they only
per-session, or global? If they are global, how do you accomplish this?
3) For that matter, is initialization per session or global?
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
I agree wholeheartedly and was actually just thinking of this yesterday. Back when I was working on NEXTGRES I implemented package support into plpgsql including scopes. While my time is pretty tight right now, I'd be more than willing to work with whoever the plpgsql master is. Jim C. Nasby wrote: On Sun, May 08, 2005 at 10:38:41PM -0500, Bob wrote: One simple benefit to packages is just organization of related code. Which, IMHO, is greatly diminished by the lack of schema.package.function notation. BTW, the original post referred to this as user.package.function, but I believe that technically it's actually schema.package.function (Oracle tends to mix schemas and users). In any case, schema.package.function is what would make sense in PostgreSQL. Personally, I think the biggest win here would be adding package support and syntax to plpgsql. Not only would it make porting from Oracle easier, it would also make plpgsql much, much more powerful. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Oracle Style packages on postgres
On Mon, May 09, 2005 at 10:05:38AM -0700, Josh Berkus wrote: I've also never much liked Oracle's seperate package_header and package_body declaration structure: if the two are intrinsically tied, why not make it one declaration? Is syntactical compatibility important enough that we need to imitate their design errors? Actually, there is a notable difference between the two. Replacing the body of a package has a minimal impact on the database, but replacing the header requires more work to invalidate cached stuff. I think there's also a few other side effects. This isn't to say that this is a good way to handle this, but I believe it's why Oracle does it. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Oracle Style packages on postgres
I would be interested in hearing how we can implement Oracle packages in
a way that seamlessly integrates into what we have. Is it like
functions that are automatically called when a schema is accessed? And
the result put into a per-session temporary schema?
I think it is unlikely we would implement Oracle packages exactly like
Oracle but I think there is interest in adding that functionality to
PostgreSQL.
If we can work up a list I can add it to the TODO list.
---
Josh Berkus wrote:
Rmm,
A black box processing engine with one or more public access functions
that retains state across calls
In other words, an Object. grin
Oracle style package creation syntax is split into header and body so that
the body(code) can be re-compiled without invalidating dependent objects.
Postgres syntax for the dbms_output example (in any postgres server side
language) would be along the lines of:
CREATE OR REPLACE PACKAGE HEADER dbms_output AS
FUNCTION dbms_output_put_line(text) RETURNS text,
FUNCTION dbms_output_get_lines() RETURNS text;
CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
language;
Hmmm. What about package variables? For me, this is one of the most
valuable parts of packages.
I've also never much liked Oracle's seperate package_header and package_body
declaration structure: if the two are intrinsically tied, why not make it one
declaration? Is syntactical compatibility important enough that we need to
imitate their design errors?
Adding pg_package with a link from pg_proc are the only changes required
to the data dictionary.
It would be nice to have similar dotted syntax as oracle
(user.package.function) but would this mess up postgres namespaces?
Yes, actually. If you look at the discussion, this is what killed the 2001
proposal; packages were proposed as orthagonal to schema which was not
acceptable.
However, now that schema are well established, it seems like this namespace
issue is limited. The problem would be that you'd have to make sure that no
two schema and packages had the same name, or that there would be an
automatic precedence of shema, package established.
So, given a shema named dataloader and a package named dataloader and a
function named copy_it(filename), what would happen is:
dataloader.dataloader.copy_it('/tmp/somefile')
... would be absolutely clear
dataloader.copy_it('/tmp/somefile')
... would attempt to call the copy_it function in the dataloader
*schema*, not the dataloader *package*.
The above seems inevitable, and not really a problem to me. We simply warn
people in the docs of the behavior, and to avoid duplicate naming.
I think there are more important questions:
1) how do you prevent users from executing the package functions outside of
the package?
2) Have you taken care of package variables? If so, are they only
per-session, or global? If they are global, how do you accomplish this?
3) For that matter, is initialization per session or global?
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] Oracle Style packages on postgres
Josh Berkus josh@agliodbs.com writes: Yes, actually. If you look at the discussion, this is what killed the 2001 proposal; packages were proposed as orthagonal to schema which was not acceptable. I think what actually killed that proposal was that it was not made clear what it did that wouldn't be done as well (and in a more standard fashion) by providing schemas. What I read in this thread is that the only truly missing feature is package variables (ie, session-local variables); is that an accurate statement? If so, it would seem simplest to add such a feature to plpgsql and be done with it. Several people already pointed out that most of the other PLs support that feature today. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Oracle Style packages on postgres
Tom, What I read in this thread is that the only truly missing feature is package variables (ie, session-local variables); is that an accurate statement? If so, it would seem simplest to add such a feature to plpgsql and be done with it. Several people already pointed out that most of the other PLs support that feature today. Also initialization, namespacing, and security. The ability to package bunches of functions, and only allow their calling in the context of a package, is quite valuable in installations which support 1,000's of procedures. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Oracle Style packages on postgres
Tom, This is exactly the sort of argumentation that got the last proposal shot down ;-). I see no reason that you can't do the namespacing and security as well or better using the existing (and more standard) schema feature. If there's something there that's not covered, what is it? a) When you have 1000's of procedures, it becomes very useful to have more than one level of namespacing. This is not an exaggeration; one project I looked at who decided not to convert from Oracle to PostgreSQL had over 100,000 procedures and functions. Lack of packages was their main reason for not switching. Schemas provide only *one* level of namespacing, unless we want to improve on the SQL standard and allow nested schemas. b) Schemas do not provide us with any way of limiting the scope of functions and persistent variables. With packages, you would want: 1. functions which can only be called internally to the package 2. variables which are only visible inside the package 3. functions which can only be called as part of the package (thus utilizing the initialization and internal variables) and not on their own. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Oracle Style packages on postgres
Josh Berkus wrote: Tom, This is exactly the sort of argumentation that got the last proposal shot down ;-). I see no reason that you can't do the namespacing and security as well or better using the existing (and more standard) schema feature. If there's something there that's not covered, what is it? a) When you have 1000's of procedures, it becomes very useful to have more than one level of namespacing. This is not an exaggeration; one project I looked at who decided not to convert from Oracle to PostgreSQL had over 100,000 procedures and functions. Lack of packages was their main reason for not switching. Schemas provide only *one* level of namespacing, unless we want to improve on the SQL standard and allow nested schemas. b) Schemas do not provide us with any way of limiting the scope of functions and persistent variables. With packages, you would want: 1. functions which can only be called internally to the package 2. variables which are only visible inside the package 3. functions which can only be called as part of the package (thus utilizing the initialization and internal variables) and not on their own. What Josh describes here are excellent features but IMHO, the Oracle PACKAGE concept is an abomination that should have been left out. The reason I say this is that Oracle also provide the ability to create user defined types that have methods. Both instance and static methods can be created. In Oracle you can use the syntax: schema.package.function() but you can just as well use the syntax: schema.type.static method() Why do you need both? If PostgreSQL is going to add new nice features that enables better namespace handling and global variables, take a look at Oracles UDT's with static and instance methods. Only thing that I'm not sure is there is static variables. If it's missing, we could add that easilly and give them the same life-span as the session. A UDT can be exchanged seamlessly across PL's so it would become a really elegant solution for session variables. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oracle Style packages on postgres
Thomas Hallgren [EMAIL PROTECTED] writes: In Oracle you can use the syntax: schema.package.function() but you can just as well use the syntax: schema.type.static method() Hmm. I think there is also something pretty close to that in SQL2003. It would be a lot easier to talk us into accepting something that's in the spec than something that isn't. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle Style packages on postgres
Josh Berkus wrote: Bruce, b) Schemas do not provide us with any way of limiting the scope of functions and persistent variables. With packages, you would want: 1. functions which can only be called internally to the package 2. variables which are only visible inside the package 3. functions which can only be called as part of the package (thus utilizing the initialization and internal variables) and not on their own. What if we defined functions to look in their own schemas for functions they call, then use the search_path, rather than using the search path first? That really doesn't address the desired functionality. For example, I could have a package whose initialization function involves some security checks, and then the package's methods (internal functions) would access the variables set by the security check function ... but those variables would NOT be available to the user or modifiable by them. I know the need for this is probably hypothetical to a lot of -hackers, but it's pretty common programming in the Oracle PL/SQL world. Of course, if there's something in SQL2003 that supports this, it would be really keen to know it ... Agreed, but saying we are going to just go out and implement everything Oracle packages have just because they have them isn't likely to happen for PostgreSQL. We need a list of things that need to be added, and how our existing functionality will be modified to make them available. Just saying we need Oracle packages doesn't make it happen. I have followed the discussion and I still don't have a clear idea of the exact additions that people want, and without that, nothing is likely to happen. I don't even have something for the TODO list at this point. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Oracle Style packages on postgres
B- Just saying we need Oracle packages doesn't make it happen. I have followed the discussion and I still don't have a clear idea of the exact additions that people want, and without that, nothing is likely to happen. I don't even have something for the TODO list at this point. That's what I'm trying to help define. I think that private variables and private functions need to be part of the definition. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Oracle Style packages on postgres
Josh Berkus wrote: B- Just saying we need Oracle packages doesn't make it happen. ?I have followed the discussion and I still don't have a clear idea of the exact additions that people want, and without that, nothing is likely to happen. ?I don't even have something for the TODO list at this point. That's what I'm trying to help define. I think that private variables and private functions need to be part of the definition. OK, so it seems we need: C static/private functions for schemas C static/private variables for schemas Are private variables implemented via the temporary per-session schema? nested schemas What does the standard say? Is that it? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Oracle Style packages on postgres
Josh Berkus wrote: Tom, This is exactly the sort of argumentation that got the last proposal shot down ;-). I see no reason that you can't do the namespacing and security as well or better using the existing (and more standard) schema feature. If there's something there that's not covered, what is it? a) When you have 1000's of procedures, it becomes very useful to have more than one level of namespacing. This is not an exaggeration; one project I looked at who decided not to convert from Oracle to PostgreSQL had over 100,000 procedures and functions. Lack of packages was their main reason for not switching. Schemas provide only *one* level of namespacing, unless we want to improve on the SQL standard and allow nested schemas. b) Schemas do not provide us with any way of limiting the scope of functions and persistent variables. With packages, you would want: 1. functions which can only be called internally to the package 2. variables which are only visible inside the package 3. functions which can only be called as part of the package (thus utilizing the initialization and internal variables) and not on their own. What if we defined functions to look in their own schemas for functions they call, then use the search_path, rather than using the search path first? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle Style packages on postgres
Bruce, b) Schemas do not provide us with any way of limiting the scope of functions and persistent variables. With packages, you would want: 1. functions which can only be called internally to the package 2. variables which are only visible inside the package 3. functions which can only be called as part of the package (thus utilizing the initialization and internal variables) and not on their own. What if we defined functions to look in their own schemas for functions they call, then use the search_path, rather than using the search path first? That really doesn't address the desired functionality. For example, I could have a package whose initialization function involves some security checks, and then the package's methods (internal functions) would access the variables set by the security check function ... but those variables would NOT be available to the user or modifiable by them. I know the need for this is probably hypothetical to a lot of -hackers, but it's pretty common programming in the Oracle PL/SQL world. Of course, if there's something in SQL2003 that supports this, it would be really keen to know it ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle Style packages on postgres
Bruce, OK, so it seems we need: C static/private functions for schemas C static/private variables for schemas Are private variables implemented via the temporary per-session schema? nested schemas What does the standard say? Is that it? Hmmm. That's an interesting approach. I, personally, would buy that. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Oracle Style packages on postgres
Oracle Style packages on postgres
OVERVIEW:
To emulate oracle server side development in postgres I required server
side packages. The following text demonstrates how to do this using
plpython on postgres 8 and suggests a language extension.
WHAT ARE ORACLE PACKAGES?
Looking back over the postgres discussion forums (particulary a discussion
in 2001 following a proposal by Bill Studenmund) there appears to be some
confusion over what oracle packages are. Here's a concise definition :
A black box processing engine with one or more public access functions
that retains state across calls
An oracle package is created when first referenced. Its initialization
code is run once (ie costly queries to populate session wide package
params) and the package dies at the end of the session
An analogy with OOP is that it's like having a single class instance
available for the duration of a session.
SOME POWERFUL USES OF PACKAGES:
1. Pipes - oracle dbms_pipe built-in allows asynchronous communication
between any number of producer/consumer database sessions on any number of
pipes
2. Logging - leave all logging/debug statements in code, decision on
logging output can be made when the logging package is initialised (eg by
querying lookup tables for user, on/off, level, and destination). Combine
logging with pipes and the output can be stored in tables seperate from
the current transaction. Include timing info down to milliseconds and
live problems/bottlenecks can more easily be identified.
3. Batch reporting - more suited to autonomous transactions than logging
but useful to have the report package store start time, duration,
error/warning count running totals etc. and summarize automatically at
report end.
See the example below on how to implement a version of the oracle
dbms_output package in plpython
EXTENSIONS TO POSTGRES:
Oracle style package creation syntax is split into header and body so that
the body(code) can be re-compiled without invalidating dependent objects.
Postgres syntax for the dbms_output example (in any postgres server side
language) would be along the lines of:
CREATE OR REPLACE PACKAGE HEADER dbms_output AS
FUNCTION dbms_output_put_line(text) RETURNS text,
FUNCTION dbms_output_get_lines() RETURNS text;
CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
language;
Adding pg_package with a link from pg_proc are the only changes required
to the data dictionary.
It would be nice to have similar dotted syntax as oracle
(user.package.function) but would this mess up postgres namespaces?
The language in which the package was created would process the 'package
code', for example in python:
o create public functions linking header declaration to package body code
(see dbms_output example)
o process embedded sql, eg l_curs=select * from dual -
l_curs=self.execute('select * from dual')
o the extracted sql can be 'prepared' by postgres and syntax exceptions
reported as compilation errors
SUMMARY:
Packages are an important addition to postgres. Some of the server side
languages have the potential to create them now. It would be useful to
add a common high level syntax before the various language implementations
start developing their own solutions.
I'm currently testing dbms_pipe on postgres, let me know if anyone is
interested. I replaced xml-rpc (5 messages/second) by sockets (600x
faster!), and may test corba
Ronnie Mackay
-
-
EXAMPLE :CONVERT ORACLE BUILT-IN PACKAGE DBMS_OUTPUT:
[Oracle syntax is :exec dbms_output.put_line('line1');]
Postgresselect dbms_output_put_line('line 1');
Postgresselect test_call_dbms_output_from_within_plpgsql('line 2
(plpgsql)');
Postgresselect test_call_dbms_output_from_within_plpython('line 3
(plpython)');
Postgresselect dbms_output_put_line('line 4');
Postgresselect dbms_output_get_lines();
--- DBMS_OUTPUT DEMO ---
line 1
line 2 (plpgsql)
line 3 (plpython)
line 4
--- DBMS_OUTPUT DEMO ---
So using current postgres syntax the only difference with oracle is that
dbms_output.put_line('line 1'); becomes
dbms_output_put_line('line 1');
The source code to implement the package body is returned by postgres
function dbms_output()
POSTGRES CREATE STATEMENTS FOR EXAMPLE:
-
CREATE or replace FUNCTION dbms_output_put_line(text) RETURNS text AS $$
from plpython import getPackage
return getPackage(GD, plpy, 'dbms_output').putLine(args[0])
$$ LANGUAGE plpythonu;
CREATE or replace FUNCTION dbms_output_get_lines() RETURNS text AS $$
from plpython import getPackage
return getPackage(GD, plpy, 'dbms_output').getLines()
$$ LANGUAGE plpythonu;
-- package body
CREATE OR REPLACE FUNCTION dbms_output() RETURNS text AS $$
return
from plpython
Re: [HACKERS] Oracle Style packages on postgres
One simple benefit to packages is just organization of related code.On 5/7/05, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:Oracle Style packages on postgresOVERVIEW:
To emulate oracle server side development in postgres I required serverside packages.The following text demonstrates how to do this usingplpython on postgres 8 and suggests a language extension.WHAT ARE ORACLE PACKAGES?
Looking back over the postgres discussion forums (particulary a discussionin 2001 following a proposal by Bill Studenmund) there appears to be someconfusion over what oracle packages are.Here's a concise definition :
A black box processing engine with one or more public access functionsthat retains state across callsAn oracle package is created when first referenced. Its initializationcode is run once (ie costly queries to populate session wide package
params) and the package dies at the end of the sessionAn analogy with OOP is that it's like having a single class instanceavailable for the duration of a session.SOME POWERFUL USES OF PACKAGES:
1. Pipes - oracle dbms_pipe built-in allows asynchronous communicationbetween any number of producer/consumer database sessions on any number ofpipes2. Logging - leave all logging/debug statements in code, decision on
logging output can be made when the logging package is initialised (eg byquerying lookup tables for user, on/off, level, and destination).Combinelogging with pipes and the output can be stored in tables seperate from
the current transaction.Include timing info down to milliseconds andlive problems/bottlenecks can more easily be identified.3. Batch reporting - more suited to autonomous transactions than loggingbut useful to have the report package store start time, duration,
error/warning count running totals etc. and summarize automatically atreport end.See the example below on how to implement a version of the oracledbms_output package in plpythonEXTENSIONS TO POSTGRES:
Oracle style package creation syntax is split into header and body so thatthe body(code) can be re-compiled without invalidating dependent objects.Postgres syntax for the dbms_output example (in any postgres server side
language) would be along the lines of:CREATE OR REPLACE PACKAGE HEADER dbms_output AS FUNCTION dbms_output_put_line(text) RETURNS text, FUNCTION dbms_output_get_lines() RETURNS text;CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
language;Adding pg_package with a link from pg_proc are the only changes requiredto the data dictionary.It would be nice to have similar dotted syntax as oracle(user.package.function) but would this mess up postgres namespaces?
The language in which the package was created would process the 'packagecode', for example in python:ocreate public functions linking header declaration to package body code(see dbms_output example)
oprocess embedded sql, eg l_curs=select * from dual -l_curs=self.execute('select * from dual')othe extracted sql can be 'prepared' by postgres and syntax exceptionsreported as compilation errors
SUMMARY:Packages are an important addition to postgres.Some of the server sidelanguages have the potential to create them now.It would be useful toadd a common high level syntax before the various language implementations
start developing their own solutions.I'm currently testing dbms_pipe on postgres, let me know if anyone isinterested.I replaced xml-rpc (5 messages/second) by sockets (600xfaster!), and may test corba
Ronnie Mackay--EXAMPLE :CONVERT ORACLE BUILT-IN PACKAGE DBMS_OUTPUT:
[Oracle syntax is :exec dbms_output.put_line('line1');]Postgresselect dbms_output_put_line('line 1');Postgresselect test_call_dbms_output_from_within_plpgsql('line 2(plpgsql)');Postgresselect test_call_dbms_output_from_within_plpython('line 3
(plpython)');Postgresselect dbms_output_put_line('line 4');Postgresselect dbms_output_get_lines();--- DBMS_OUTPUT DEMO ---line 1line 2 (plpgsql)line 3 (plpython)
line 4--- DBMS_OUTPUT DEMO ---So using current postgres syntax the only difference with oracle is thatdbms_output.put_line('line 1'); becomesdbms_output_put_line('line 1');
The source code to implement the package body is returned by postgresfunction dbms_output()POSTGRES CREATE STATEMENTS FOR EXAMPLE:-
CREATE or replace FUNCTION dbms_output_put_line(text) RETURNS text AS $$from plpython import getPackagereturn getPackage(GD, plpy, 'dbms_output').putLine(args[0])$$ LANGUAGE plpythonu;CREATE or replace FUNCTION dbms_output_get_lines() RETURNS text AS $$
from plpython import getPackagereturn getPackage(GD, plpy, 'dbms_output').getLines()$$ LANGUAGE plpythonu;-- package bodyCREATE OR REPLACE FUNCTION dbms_output() RETURNS text AS $$return
from plpython import PlPythonPackageclass Package(PlPythonPackage): def __init__(self,
Re: [HACKERS] Oracle Style packages on postgres
Bob wrote:
(B One simple benefit to packages is just organization of related code.
(B
(BAnd the package-scoped variables or constant values, similar to
(Bthe global variables.
(B
(BIt will be very useful for application programmers
(Bif one variable can be shared from several functions.
(B
(BI needed some tricks when I tried to port such PL/SQL to PL/pgSQL.
(B
(BBob wrote:
(B One simple benefit to packages is just organization of related code.
(B
(B On 5/7/05, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]* [EMAIL PROTECTED]
(B mailto:[EMAIL PROTECTED] wrote:
(B
(B Oracle Style packages on postgres
(B
(B OVERVIEW:
(B
(B To emulate oracle server side development in postgres I required server
(B side packages. The following text demonstrates how to do this using
(B plpython on postgres 8 and suggests a language extension.
(B
(B WHAT ARE ORACLE PACKAGES?
(B
(B Looking back over the postgres discussion forums (particulary a
(B discussion
(B in 2001 following a proposal by Bill Studenmund) there appears to be
(B some
(B confusion over what oracle packages are. Here's a concise definition :
(B"A black box processing engine with one or more public access
(B functions
(B that retains state across calls"
(B An oracle package is created when first referenced. Its initialization
(B code is run once (ie costly queries to populate session wide package
(B params) and the package dies at the end of the session
(B An analogy with OOP is that it's like having a single class instance
(B available for the duration of a session.
(B
(B SOME POWERFUL USES OF PACKAGES:
(B
(B 1. Pipes - oracle dbms_pipe built-in allows asynchronous communication
(B between any number of producer/consumer database sessions on any
(B number of
(B pipes
(B
(B 2. Logging - leave all logging/debug statements in code, decision on
(B logging output can be made when the logging package is initialised
(B (eg by
(B querying lookup tables for user, on/off, level, and
(B destination). Combine
(B logging with pipes and the output can be stored in tables seperate from
(B the current transaction. Include timing info down to milliseconds and
(B live problems/bottlenecks can more easily be identified.
(B
(B 3. Batch reporting - more suited to autonomous transactions than logging
(B but useful to have the report package store start time, duration,
(B error/warning count running totals etc. and summarize automatically at
(B report end.
(B
(B See the example below on how to implement a version of the oracle
(B dbms_output package in plpython
(B
(B EXTENSIONS TO POSTGRES:
(B
(B Oracle style package creation syntax is split into header and body
(B so that
(B the body(code) can be re-compiled without invalidating dependent
(B objects.
(B Postgres syntax for the dbms_output example (in any postgres server
(B side
(B language) would be along the lines of:
(B CREATE OR REPLACE PACKAGE HEADER dbms_output AS
(BFUNCTION dbms_output_put_line(text) RETURNS text,
(BFUNCTION dbms_output_get_lines() RETURNS text;
(B CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
(B language;
(B
(B Adding pg_package with a link from pg_proc are the only changes required
(B to the data dictionary.
(B It would be nice to have similar dotted syntax as oracle
(B (user.package.function) but would this mess up postgres namespaces?
(B
(B The language in which the package was created would process the 'package
(B code', for example in python:
(B o create public functions linking header declaration to package
(B body code
(B (see dbms_output example)
(B o process embedded sql, eg l_curs=select * from dual -
(B l_curs=self.execute('select * from dual')
(B o the extracted sql can be 'prepared' by postgres and syntax exceptions
(B reported as compilation errors
(B
(B SUMMARY:
(B Packages are an important addition to postgres. Some of the server side
(B languages have the potential to create them now. It would be useful to
(B add a common high level syntax before the various language
(B implementations
(B start developing their own solutions.
(B
(B I'm currently testing dbms_pipe on postgres, let me know if anyone is
(B interested. I replaced xml-rpc (5 messages/second) by sockets (600x
(B faster!), and may test corba
(B
(B Ronnie Mackay
(B
(B
(B -
(B
(B -
(B
(B EXAMPLE :CONVERT ORACLE BUILT-IN PACKAGE DBMS_OUTPUT:
(B
(B [Oracle syntax is :exec dbms_output.put_line('line1');]
(B
Re: [HACKERS] Oracle Style packages on postgres
Satoshi Nagayasu wrote: (B (BAn oracle package is created when first referenced. Its initialization (Bcode is run once (ie costly queries to populate session wide package (Bparams) and the package dies at the end of the session (BAn analogy with OOP is that it's like having a single class instance (Bavailable for the duration of a session. (B (BPL/Java has an object called "Session" that does exactly this. It is not (Bavailable from other languages at present. Are Packages supposed to be (Bcross-language? (B (BRegards, (BThomas Hallgren (B (B (B---(end of broadcast)--- (BTIP 9: the planner will ignore your desire to choose an index scan if your (B joining column's datatypes do not match
