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 relationbetween
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 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.
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/
Import Notes
Resolved by subject fallback
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/
Show quoted text
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 etcThis 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/
