Обсуждение: 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.stehule@hotmail.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@pervasive.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.stehule@hotmail.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.stehule@hotmail.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@sss.pgh.pa.us>
>To: "Pavel Stehule" <pavel.stehule@hotmail.com>
>CC: jnasby@pervasive.com, dev@archonet.com, pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] proposal for PL packages for 8.3. Date: Tue, 08 Aug 
>2006 08:18:42 -0400
>
>"Pavel Stehule" <pavel.stehule@hotmail.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.stehule@hotmail.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@momjian.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@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

----- Original Message ----- 
From: "Bruce Momjian" <bruce@momjian.us>
To: "Josh Berkus" <josh@agliodbs.com>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Pavel Stehule" 
<pavel.stehule@hotmail.com>; <dev@archonet.com>; 
<pgsql-hackers@postgresql.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
privatepackage 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. 

>   Is it would be nice , if packages have been ;

>   1. Package level variables (Public variables)

is very hard for imlementation, and it's actually impossible. Needs large 
changes in code
>   2. Package member level variables (Private variable)

I plan it, in every PL language
>   3. Public and private package members
?? I see sence only for functions. I don't wont supply schemas.

>4. Syntax must be as closer as plpgsql (declaration, assingment etc) rather 
>than any syntax that we have to learn :-)
PostgreSQL support other languages than PL/pgSQL. We need universal syntax 
for plperl and others too

Pavel

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

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@svana.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@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

<blockquote type="CITE"><pre>
<font color="#000000">> Well, it would be nice to have some clarification about the expected</font>
<font color="#000000">> scope and lifetimes of these variables. If two different sessions</font>
<font color="#000000">> change the values, what's supposed to happen?</font>

<font color="#000000">Right, I am confused whether these are session or schema-local</font>
<font color="#000000">variables.  What does Oracle support?  Looking at this:</font>

<font color="#000000">    <a
href="http://thinkoracle.blogspot.com/2005/10/oracle-packages.html">http://thinkoracle.blogspot.com/2005/10/oracle-packages.html</a></font>

<font color="#000000">it seems varaiable are per-session.</font>
</pre></blockquote><br /> Package variables are per-session.  <br /><br /> Don't forget package initializers too...<br
/><tablecellpadding="0" cellspacing="0" width="100%"><tr><td><br /><br /> --<br />   Korry Douglas    <a
href="mailto:korryd@enterprisedb.com">korryd@enterprisedb.com</a><br/>   EnterpriseDB      <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a></td></tr></table>

>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@pervasive.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@pervasive.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 06:34:16AM +0200, Pavel Stehule wrote:
> >  Is it would be nice , if packages have been ;
> 
> >  1. Package level variables (Public variables)
> 
> is very hard for imlementation, and it's actually impossible. Needs large 
> changes in code
> >  2. Package member level variables (Private variable)
> 
> I plan it, in every PL language
> >  3. Public and private package members
> ?? I see sence only for functions. I don't wont supply schemas.
> 
> >4. Syntax must be as closer as plpgsql (declaration, assingment etc) 
> >rather than any syntax that we have to learn :-)
> PostgreSQL support other languages than PL/pgSQL. We need universal syntax 
> for plperl and others too

Why? Don't those other languages have support of their own for this?

If we try and make this completely cross-language I fear we'll end up
with something so watered down and obtuse that it'll be useless. I think
it makes much more sense to design something for plpgsql and only
commonize whatever it makes sense to.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

>From: "Jim C. Nasby" <jnasby@pervasive.com>
>To: Pavel Stehule <pavel.stehule@hotmail.com>
>CC: a_dursun@hotmail.com, pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] proposal for PL packages for 8.3.
>Date: Wed, 9 Aug 2006 11:27:01 -0500
>
>On Wed, Aug 09, 2006 at 06:34:16AM +0200, Pavel Stehule wrote:
> > >  Is it would be nice , if packages have been ;
> >
> > >  1. Package level variables (Public variables)
> >
> > is very hard for imlementation, and it's actually impossible. Needs 
>large
> > changes in code
> > >  2. Package member level variables (Private variable)
> >
> > I plan it, in every PL language
> > >  3. Public and private package members
> > ?? I see sence only for functions. I don't wont supply schemas.
> >
> > >4. Syntax must be as closer as plpgsql (declaration, assingment etc)
> > >rather than any syntax that we have to learn :-)
> > PostgreSQL support other languages than PL/pgSQL. We need universal 
>syntax
> > for plperl and others too
>
>Why? Don't those other languages have support of their own for this?
>
>If we try and make this completely cross-language I fear we'll end up
>with something so watered down and obtuse that it'll be useless. I think
>it makes much more sense to design something for plpgsql and only
>commonize whatever it makes sense to.

What I know, plperl has shared variables. Missing shared functions and 
loader for initialisation of variables. I don't agree with partial solution 
only for plpgsql. It can generate more limits in future.

Pavel

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. 
http://www.msn.cz/

Jim C. Nasby wrote:
>>> 4. Syntax must be as closer as plpgsql (declaration, assingment etc) 
>>> rather than any syntax that we have to learn :-)
>>>       
>> PostgreSQL support other languages than PL/pgSQL. We need universal syntax 
>> for plperl and others too
>>     
>
> Why? Don't those other languages have support of their own for this?
>
> If we try and make this completely cross-language I fear we'll end up
> with something so watered down and obtuse that it'll be useless. I think
> it makes much more sense to design something for plpgsql and only
> commonize whatever it makes sense to.
>   

plperl and pltcl at least have support for now for shared non-table 
session data. The trouble is that it is shared ONLY inside the 
interpreter. That means there is no sharing between, say, a plperl func 
and a pltcl func. Now it would make far more sense if session objects 
could be shared between interpreters, especially if they are namespace 
scoped. So I think you need to give a good reason for NOT sharing.

cheers

andrew