Re: proposal for PL packages for 8.3.

Hello

Package contains package's variables, shared protected functions and
initialisation part, which is executed when any public function attached to
package is called. Every package has defined only one language. The reason
for this is binary compatibility of package's variables. Private functions
aren't SQL functions and it isn't possible to call them via SPI. Because
PL/pgSQL can't call functions via different interface than SPI, PL/pgSQL
won't support private functions. Package owner can attach any SQL funtions
to package, even those written in different language, but only functions in
same language as package can access package variables and protect functions.
Package is similar to schema. Public package function can access package
variables or private functions only if it has same language as package.
Every function can be attached to just one package. Only owner can modify
package (ALTER OR ATTACH functions).

Samples:

CREATE OR REPLACE PACKAGE foo_package
AS $$
DECLARE my_var integer;
BEGIN
my_var := 0;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION foo_package.counter() RETURNS integer
AS $$
BEGIN
my_var := my_var + 1;
RETURN foo_package.my_var; -- explicit namespace
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT foo_package.counter();

This proposal for package has minimal impact on current implementation of PL
languages.

CREATE OR REPLACE PACKAGE xml_parser AS $$
USE XML::LibXML;

my $parser = XML::LibXML->new;
my $schema_file = '/home/postgres/comm.xsd';
my $schema = XML::LibXML::Schema->new(location => $schema_file);

sub get_data
{
my ($root, $tag, $optional) = @_;
my $aux = $root->getElementsByTag($tag);
if (@aux eq 0)
{
elog(ERROR, "Missing value") if ! $optional;
return undef;
}
return @aux[0]->getFirstChild->getData;
}
$$ LANGUAGE plperlu;

CREATE OR REPLACE FUNCTION xml_parser.parse_document (
IN body varchar,
OUT name varchar,
OUT document_type varchar)
AS $$
my $body = $_[0];

my $doc = $parser->parse_string($body);
$schema->validate($doc);
$root = $doc->getDocumentElement();
return {
name => $root->nodeName;
document_type => get_data($root, 'type') };
$$ LANGUAGE plperlu;

- using different language, can access only public functions
CREATE OR REPLACE FUNCTION xml_parser.validate_all_doc(
OUT _name varchar,
OUT _state boolean) RETURNS SETOF RECORD
AS $$
DECLARE _body varchar;
BEGIN
FOR _body, _name IN SELECT body, path FROM xml_repository LOOP
BEGIN
-- use implicit search_path containing package_name
_state := true;
PERFORM parse_document(_r.body);
EXCEPTION WHEN OTHERS THEN
_state := false;
END;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT xml_parser.validate_all_doc();

This system is simple and minimalistic and doesn't copy package system from
ADA or Oracle which are more complicated and don't allow multiple PL.

Any comments are welcome

Regards
Pavel Stehule

p.s. I'll have free time on sept. and can work on it.

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

Pavel Stehule wrote:
> Package is similar to schema.

Are you saying that the package would effectively *be* a schema from the
outside. That is, if I have package "foo" then I can't also have a
schema "foo"?

--
Richard Huxton
Archonet Ltd

>
>Pavel Stehule wrote:
>>Package is similar to schema.
>
>Are you saying that the package would effectively *be* a schema from the
>outside. That is, if I have package "foo" then I can't also have a schema
>"foo"?
>

Yes, because I don't need duplicity in function's names.

Pavel

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

"Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> writes:
>> Are you saying that the package would effectively *be* a schema from the
>> outside. That is, if I have package "foo" then I can't also have a schema
>> "foo"?

> Yes, because I don't need duplicity in function's names.

What if the package needs some tables associated with it? I think you
need to think harder about the relationship of packages and schemas.
I don't necessarily object to merging the concepts like this, but
the implications look a bit messy at first sight.

regards, tom lane

> >> Are you saying that the package would effectively *be* a schema from
>the
> >> outside. That is, if I have package "foo" then I can't also have a
>schema
> >> "foo"?
>
> > Yes, because I don't need duplicity in function's names.
>
>What if the package needs some tables associated with it? I think you
>need to think harder about the relationship of packages and schemas.
>I don't necessarily object to merging the concepts like this, but
>the implications look a bit messy at first sight.
>
> regards, tom lane

What is problem? I can attach table or sequence. What can be problem is
visibility of nesteded objects (if can be different than functions). My
proposal is only concept, and I my first goal is find way for secure storing
session's variables and shared native functions, like my sample. I didn't
think about others objecst and it's maybe error. Or maybe I was wrong in
"package is similar to schema". I wonted say so relation between function
and package is very similar to relation between functions and schema.

Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

On Mon, Aug 07, 2006 at 03:57:05PM +0200, Pavel Stehule wrote:
> >>> Are you saying that the package would effectively *be* a schema from
> >the
> >>> outside. That is, if I have package "foo" then I can't also have a
> >schema
> >>> "foo"?
> >
> >> Yes, because I don't need duplicity in function's names.
> >
> >What if the package needs some tables associated with it? I think you
> >need to think harder about the relationship of packages and schemas.
> >I don't necessarily object to merging the concepts like this, but
> >the implications look a bit messy at first sight.
> >
> > regards, tom lane
>
> What is problem? I can attach table or sequence. What can be problem is
> visibility of nesteded objects (if can be different than functions). My
> proposal is only concept, and I my first goal is find way for secure
> storing session's variables and shared native functions, like my sample. I
> didn't think about others objecst and it's maybe error. Or maybe I was
> wrong in "package is similar to schema". I wonted say so relation between
> function and package is very similar to relation between functions and
> schema.

Having the relationship be similar is fine... actually implimenting
packages as some special kind of schema sounds like a really bad idea.
IMHO, packages should themselves be first-level objects that reside
under schemas. Of course that raises some interesting questions about
the visibility of the functions inside a package, which is why IIRC the
last time this was brought up one of the ideas was to extend schemas so
that they could contain other schemas.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

> >
> > What is problem? I can attach table or sequence. What can be problem is
> > visibility of nesteded objects (if can be different than functions). My
> > proposal is only concept, and I my first goal is find way for secure
> > storing session's variables and shared native functions, like my sample.
>I
> > didn't think about others objecst and it's maybe error. Or maybe I was
> > wrong in "package is similar to schema". I wonted say so relation
>between
> > function and package is very similar to relation between functions and
> > schema.
>
>Having the relationship be similar is fine... actually implimenting
>packages as some special kind of schema sounds like a really bad idea.
>IMHO, packages should themselves be first-level objects that reside
>under schemas. Of course that raises some interesting questions about
>the visibility of the functions inside a package, which is why IIRC the
>last time this was brought up one of the ideas was to extend schemas so
>that they could contain other schemas.

I unlike concept of nested schemats or packages nested in schema. I don't
see reason for it. About implementation.. package is more special kind of
function for me. But relation between package and function I can create via
dot notation in function's name. It's different from nested syntax from
PL/SQL or ADA. I can easy separate SQL part and non SQL part.

Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

"Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> writes:
> I unlike concept of nested schemats or packages nested in schema. I don't
> see reason for it. About implementation.. package is more special kind of
> function for me. But relation between package and function I can create via
> dot notation in function's name. It's different from nested syntax from
> PL/SQL or ADA. I can easy separate SQL part and non SQL part.

Apparently you're not aware that that syntax is not free for the taking.
The reason people are complaining about this proposal is that currently
foo.bar(...) means function bar in schema foo, and you seem to be
intending to break it.

regards, tom lane

>
>"Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> writes:
> > I unlike concept of nested schemats or packages nested in schema. I
>don't
> > see reason for it. About implementation.. package is more special kind
>of
> > function for me. But relation between package and function I can create
>via
> > dot notation in function's name. It's different from nested syntax from
> > PL/SQL or ADA. I can easy separate SQL part and non SQL part.
>
>Apparently you're not aware that that syntax is not free for the taking.
>The reason people are complaining about this proposal is that currently
>foo.bar(...) means function bar in schema foo, and you seem to be
>intending to break it.
>
I understand it. But I don't know better solution. Certainly foo.bar(..) is
ambigous and it can mean both. ANSI SQL don't use packages and Oracle's
package are unsolveable because we have separated parsers. Do you have any
idea, what is good model for it?

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

>From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>To: "Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com>
>CC: jnasby(at)pervasive(dot)com, dev(at)archonet(dot)com, pgsql-hackers(at)postgresql(dot)org
>Subject: Re: [HACKERS] proposal for PL packages for 8.3. Date: Tue, 08 Aug
>2006 08:18:42 -0400
>
>"Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> writes:
> > I unlike concept of nested schemats or packages nested in schema. I
>don't
> > see reason for it. About implementation.. package is more special kind
>of
> > function for me. But relation between package and function I can create
>via
> > dot notation in function's name. It's different from nested syntax from
> > PL/SQL or ADA. I can easy separate SQL part and non SQL part.
>
>Apparently you're not aware that that syntax is not free for the taking.
>The reason people are complaining about this proposal is that currently
>foo.bar(...) means function bar in schema foo, and you seem to be
>intending to break it.
>
> regards, tom lane

I found some doc about it, but I confused. Oracle has two similar kind of
objects: packages and modules. Ansi SQL defines MODULES.
http://64.233.183.104/search?q=cache:jkXyiDKg-sgJ:www.oracle.com/technology/products/rdb/pdf/createmodule_external_routines.pdf+%22CREATE+MODULE%22+sql&hl=cs&ct=clnk&cd=4

Has anybody more documentation about it?

Regards
Pavel Stehule

_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/

Tom Lane wrote:
> "Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> writes:
> >> Are you saying that the package would effectively *be* a schema from the
> >> outside. That is, if I have package "foo" then I can't also have a schema
> >> "foo"?
>
> > Yes, because I don't need duplicity in function's names.
>
> What if the package needs some tables associated with it? I think you
> need to think harder about the relationship of packages and schemas.
> I don't necessarily object to merging the concepts like this, but
> the implications look a bit messy at first sight.

I like the idea of a package being a schema. I imagine that a package
would put its own schema name first in the 'search_path' before
referencing an object. I think anything more complex is going to be too
hard to use.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom,

I'm confused. I thought the consensus was that we'd get package
functionality via SQL99 TYPEs, rather than by implementing
oracle-copycat syntax.

--Josh

Bruce,

> I like the idea of a package being a schema. I imagine that a package
> would put its own schema name first in the 'search_path' before
> referencing an object. I think anything more complex is going to be too
> hard to use.
>

Or we could just add local variables to schema and dispense with
PACKAGES entirely.

--Josh

Josh Berkus wrote:
> Bruce,
>
> > I like the idea of a package being a schema. I imagine that a package
> > would put its own schema name first in the 'search_path' before
> > referencing an object. I think anything more complex is going to be too
> > hard to use.
> >
>
> Or we could just add local variables to schema and dispense with
> PACKAGES entirely.

Sure, makes more sense to me. I don't think people want Oracle syntax
as much as Oracle packages capabilities.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

----- Original Message -----
From: "Bruce Momjian" <bruce(at)momjian(dot)us>
To: "Josh Berkus" <josh(at)agliodbs(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; "Pavel Stehule"
<pavel(dot)stehule(at)hotmail(dot)com>; <dev(at)archonet(dot)com>;
<pgsql-hackers(at)postgresql(dot)org>
Sent: Wednesday, August 09, 2006 1:49 AM
Subject: Re: [HACKERS] proposal for PL packages for 8.3.

>>>
>>> Or we could just add local variables to schema and dispense with
>>> PACKAGES entirely.
>>
>> Sure, makes more sense to me. I don't think people want Oracle syntax
>> as much as Oracle packages capabilities.

Is it would be nice , if packages have been ;

1. Package level variables (Public variables)
2. Package member level variables (Private variable)
3. Public and private package members
4. Syntax must be as closer as plpgsql (declaration, assingment etc)
rather than any syntax that we have to learn :-)

Best regards

Adnan DURSUN
ASRIN Bilisim Ltd.

Bruce Momjian wrote:
> Josh Berkus wrote:
>> Bruce,
>>
>>> I like the idea of a package being a schema. I imagine that a package
>>> would put its own schema name first in the 'search_path' before
>>> referencing an object. I think anything more complex is going to be too
>>> hard to use.
>>>
>> Or we could just add local variables to schema and dispense with
>> PACKAGES entirely.
>
> Sure, makes more sense to me. I don't think people want Oracle syntax
> as much as Oracle packages capabilities.

There are three separate issues we seem to be talking about.

1. Namespaces - visibility or otherwise of objects
2. Procedural state - something that looks like a shared variable
3. Packaging - installation/dependency handling

Namespaces
Given that we already have search_path it makes sense to use it. So, we
could have something like:
1. A "PRIVATE" modifier for objects that mean they are only accessible
if their schema is the first in the search_path.
2. A definable search_path for a schema, so all objects have that
setting by default. In theory, this could break dynamic queries in
functions that relied on a changeable search_path.

Procedural state
Just a convenient way of defining some small amount of state. Will need
session variables and static shared variables. Presumably we'll need to
be able to lock shared variables.

Packaging
I'd guess we'd need a pg_package and pg_package_items system tables. We
could track:
- package name (different from schema)
- version number
- install/uninstall functions
- start-session/end-session functions
- dependencies (is pg_depend enough)
pg_package_items
- schema-name
- variables, functions, tables, views etc

--
Richard Huxton
Archonet Ltd

On Wed, Aug 09, 2006 at 08:38:22AM +0100, Richard Huxton wrote:
> There are three separate issues we seem to be talking about.
>
> 1. Namespaces - visibility or otherwise of objects
> 2. Procedural state - something that looks like a shared variable
> 3. Packaging - installation/dependency handling

Well, it would be nice to have some clarification about the expected
scope and lifetimes of these variables. If two different sessions
change the values, what's supposed to happen?

> Namespaces
> Given that we already have search_path it makes sense to use it. So, we
> could have something like:
> 1. A "PRIVATE" modifier for objects that mean they are only accessible
> if their schema is the first in the search_path.
> 2. A definable search_path for a schema, so all objects have that
> setting by default. In theory, this could break dynamic queries in
> functions that relied on a changeable search_path.

Having a per-function search path has been discussed before, primarily
on the basis that having the search_path affect the results of
functions defined previously is wierd, except you might actually want
this sometimes.

It hasn't been done because its expensive. As long as the search_path
doesn't change, you can cache the results of lookups. Under the current
system, a frequently changing search_path would be bad for performence.

> Procedural state
> Just a convenient way of defining some small amount of state. Will need
> session variables and static shared variables. Presumably we'll need to
> be able to lock shared variables.

Lock? That sounds like a recipe for deadlocks to me. What do people
want to use these variables for anyway?

> Packaging
> I'd guess we'd need a pg_package and pg_package_items system tables. We
> could track:
> - package name (different from schema)
> - version number
> - install/uninstall functions
> - start-session/end-session functions
> - dependencies (is pg_depend enough)
> pg_package_items
> - schema-name
> - variables, functions, tables, views etc

This has been discussed before. In particular, you can probably use the
pg_depends table to track items installed by a package, thus dispensing
with the pg_package_items. The biggest stumbling block was finding
something general enough. Oh yeah, and coding it. In particular,
consider being able to install stuff in contrib as a package, so you
can easily uninstall it.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Wed, Aug 09, 2006 at 08:38:22AM +0100, Richard Huxton wrote:
>
>> Namespaces
>> Given that we already have search_path it makes sense to use it. So, we
>> could have something like:
>> 1. A "PRIVATE" modifier for objects that mean they are only accessible
>> if their schema is the first in the search_path.
>> 2. A definable search_path for a schema, so all objects have that
>> setting by default. In theory, this could break dynamic queries in
>> functions that relied on a changeable search_path.
>
> Having a per-function search path has been discussed before, primarily
> on the basis that having the search_path affect the results of
> functions defined previously is wierd, except you might actually want
> this sometimes.
>
> It hasn't been done because its expensive. As long as the search_path
> doesn't change, you can cache the results of lookups. Under the current
> system, a frequently changing search_path would be bad for performence.

That's why I was thinking per-schema. It would mean multiple caches of
course, but unless you have a pathological number of schemas it should
be efficient enough.

>> Procedural state
>> Just a convenient way of defining some small amount of state. Will need
>> session variables and static shared variables. Presumably we'll need to
>> be able to lock shared variables.
>
> Lock? That sounds like a recipe for deadlocks to me. What do people
> want to use these variables for anyway?

I'd like session vars such as applicaton_user, application_session_id
etc. You can work around this with pl-tcl/perl functions and/or temp
tables at the moment, but it would be more convenient to use a variable.

However, if we allow variables that are visible outside a single backend
then they'll need the usual concurrency controls - at least some way to
atomically read-and-update. Now, it might be that we can just use a
table to back up shared variables (store a text representation of their
value) in which case we get good known semantics for free.

>> Packaging
>> I'd guess we'd need a pg_package and pg_package_items system tables. We
>> could track:
>> - package name (different from schema)
>> - version number
>> - install/uninstall functions
>> - start-session/end-session functions
>> - dependencies (is pg_depend enough)
>> pg_package_items
>> - schema-name
>> - variables, functions, tables, views etc
>
> This has been discussed before. In particular, you can probably use the
> pg_depends table to track items installed by a package, thus dispensing
> with the pg_package_items. The biggest stumbling block was finding
> something general enough. Oh yeah, and coding it. In particular,
> consider being able to install stuff in contrib as a package, so you
> can easily uninstall it.

Just what I was thinking - if most of contrib can't easily be put into
packages then the package isn't useful enough.

--
Richard Huxton
Archonet Ltd

>There are three separate issues we seem to be talking about.
>
>1. Namespaces - visibility or otherwise of objects
>2. Procedural state - something that looks like a shared variable
>3. Packaging - installation/dependency handling
>
and 4. support more languages:
4a) binary incompatibility between variables different PL
4b) two types for calling functions [native and SPI]

>Namespaces
>Given that we already have search_path it makes sense to use it. So, we
>could have something like:
>1. A "PRIVATE" modifier for objects that mean they are only accessible if
>their schema is the first in the search_path.
>2. A definable search_path for a schema, so all objects have that setting
>by default. In theory, this could break dynamic queries in functions that
>relied on a changeable search_path.
>
>Procedural state
>Just a convenient way of defining some small amount of state. Will need
>session variables and static shared variables. Presumably we'll need to be
>able to lock shared variables.
>
>Packaging
>I'd guess we'd need a pg_package and pg_package_items system tables. We
>could track:
>- package name (different from schema)
>- version number
>- install/uninstall functions
>- start-session/end-session functions
>- dependencies (is pg_depend enough)
>pg_package_items
>- schema-name
>- variables, functions, tables, views etc
>
it's strong but little bit complicated system. Start session and end
session is better to solve via session's triggers. Install, uninstall, +/- I
can understand sence, but I can call it manually. I need loader of package
which is evaluated when somebody first call any function from package. This
loader can initialize and create package variables (Perl don't has
variable's declaration). Can somebody say what is ANSI SQL? I haven't text
of it :-(. I found only basic syntax of "CREATE MODULE".

I thinking abaut session or schema variables. Which solve some kind of
problems. It's need some changes in parser, and part of code plpgsql can be
moved into parser.

Pavel

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

Richard Huxton wrote:
>
> Packaging
> I'd guess we'd need a pg_package and pg_package_items system tables. We
> could track:
> - package name (different from schema)
> - version number
> - install/uninstall functions
> - start-session/end-session functions
> - dependencies (is pg_depend enough)
> pg_package_items
> - schema-name
> - variables, functions, tables, views etc

While I'm thinking of it:

pg_dump needs to either dump a package as a complete package, or be able
to replace them with an instruction to import the relevant package
(where there is no associated data, just functions).

--
Richard Huxton
Archonet Ltd

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Wed, Aug 09, 2006 at 08:38:22AM +0100, Richard Huxton wrote:
> > There are three separate issues we seem to be talking about.
> >
> > 1. Namespaces - visibility or otherwise of objects
> > 2. Procedural state - something that looks like a shared variable
> > 3. Packaging - installation/dependency handling
>
> Well, it would be nice to have some clarification about the expected
> scope and lifetimes of these variables. If two different sessions
> change the values, what's supposed to happen?

Right, I am confused whether these are session or schema-local
variables. What does Oracle support? Looking at this:

http://thinkoracle.blogspot.com/2005/10/oracle-packages.html

it seems varaiable are per-session.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

> > Well, it would be nice to have some clarification about the expected
> > scope and lifetimes of these variables. If two different sessions
> > change the values, what's supposed to happen?
>
> Right, I am confused whether these are session or schema-local
> variables. What does Oracle support? Looking at this:
>
> http://thinkoracle.blogspot.com/2005/10/oracle-packages.html
>
> it seems varaiable are per-session.

Package variables are per-session.

Don't forget package initializers too...

--
Korry Douglas korryd(at)enterprisedb(dot)com
EnterpriseDB http://www.enterprisedb.com

>Martijn van Oosterhout wrote:
>-- Start of PGP signed section.
> > On Wed, Aug 09, 2006 at 08:38:22AM +0100, Richard Huxton wrote:
> > > There are three separate issues we seem to be talking about.
> > >
> > > 1. Namespaces - visibility or otherwise of objects
> > > 2. Procedural state - something that looks like a shared variable
> > > 3. Packaging - installation/dependency handling
> >
> > Well, it would be nice to have some clarification about the expected
> > scope and lifetimes of these variables. If two different sessions
> > change the values, what's supposed to happen?
>
>Right, I am confused whether these are session or schema-local
>variables. What does Oracle support? Looking at this:
>
> http://thinkoracle.blogspot.com/2005/10/oracle-packages.html
>
>it seems varaiable are per-session.
>

Oracle use packages dbms_pipe and dbms_alert for interprocess communication,
not shared variables. For both packages exists emulation in orafunc on
pgfoundry.

regards
Pavel

_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/

On Wed, Aug 09, 2006 at 10:55:30AM -0400, Bruce Momjian wrote:
> Martijn van Oosterhout wrote:
> -- Start of PGP signed section.
> > On Wed, Aug 09, 2006 at 08:38:22AM +0100, Richard Huxton wrote:
> > > There are three separate issues we seem to be talking about.
> > >
> > > 1. Namespaces - visibility or otherwise of objects
> > > 2. Procedural state - something that looks like a shared variable
> > > 3. Packaging - installation/dependency handling
> >
> > Well, it would be nice to have some clarification about the expected
> > scope and lifetimes of these variables. If two different sessions
> > change the values, what's supposed to happen?
>
> Right, I am confused whether these are session or schema-local
> variables. What does Oracle support? Looking at this:
>
> http://thinkoracle.blogspot.com/2005/10/oracle-packages.html
>
> it seems varaiable are per-session.

Yes, everything in PLSQL is per-session. I don't know of any way to
share data between sessions in PLSQL short of using a (real) table.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Wed, Aug 09, 2006 at 11:48:41AM +0200, Pavel Stehule wrote:
>
> >There are three separate issues we seem to be talking about.
> >
> >1. Namespaces - visibility or otherwise of objects
> >2. Procedural state - something that looks like a shared variable
> >3. Packaging - installation/dependency handling
> >
> and 4. support more languages:
> 4a) binary incompatibility between variables different PL
> 4b) two types for calling functions [native and SPI]

I see a lot less use for more languages, since other languages already
have most of the features of a package.

> >Namespaces
> >Given that we already have search_path it makes sense to use it. So, we
> >could have something like:
> >1. A "PRIVATE" modifier for objects that mean they are only accessible if
> >their schema is the first in the search_path.
> >2. A definable search_path for a schema, so all objects have that setting
> >by default. In theory, this could break dynamic queries in functions that
> >relied on a changeable search_path.

ISTM that messing with search_path is a lot more obtuse than simply
labeling something as PRIVATE or PUBLIC when you define it.

Oracle's handling of packages is actually quite elegant; it's worth
looking at for anyone who's not familiar with it. In a nutshell, it
looks something like this:

CREATE OR REPLACE PACKAGE foo (

declare global variables;
-- Variables can be public or private, but the accepted practice is to
-- make them all private and define accessor functions for them

BEGIN;
-- block of code to be executed on first invocation
END;

CREATE FUNCTION public_function() ...

CREATE PRIVATE FUNCTION bar();
);

This is actually split into two parts, a package header and a package
body. The header defines all the external "API".. what functions are
public, their parameters, etc. The body contains the actual code. My
guess is this was done so that you can change the body at will without
invalidating all the plan caches in the entire database.

> >Procedural state
> >Just a convenient way of defining some small amount of state. Will need
> >session variables and static shared variables. Presumably we'll need to be
> >able to lock shared variables.
> >
> >Packaging
> >I'd guess we'd need a pg_package and pg_package_items system tables. We
> >could track:
> >- package name (different from schema)
> >- version number
> >- install/uninstall functions
> >- start-session/end-session functions
> >- dependencies (is pg_depend enough)
> >pg_package_items
> >- schema-name
> >- variables, functions, tables, views etc
> >
> it's strong but little bit complicated system. Start session and end
> session is better to solve via session's triggers. Install, uninstall, +/-
> I can understand sence, but I can call it manually. I need loader of
> package which is evaluated when somebody first call any function from
> package. This loader can initialize and create package variables (Perl
> don't has variable's declaration). Can somebody say what is ANSI SQL? I
> haven't text of it :-(. I found only basic syntax of "CREATE MODULE".

Oracle has no concept of a 'session' functions. It only allows you to
run a block of code the first time a package is called in a session, so
that you can do setup.

I'm not really sure what you'd use install/uninstall functions for.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461