Oracle Style packages on postgres

One simple benefit to packages is just organization of related code.

Bob wrote:

One simple benefit to packages is just organization of related code.

And the package-scoped variables or constant values, similar to
the global variables.

It will be very useful for application programmers
if one variable can be shared from several functions.

I needed some tricks when I tried to port such PL/SQL to PL/pgSQL.

Bob wrote:

One simple benefit to packages is just organization of related code.

On 5/7/05, *rmm@sqlisor.com <mailto:rmm@sqlisor.com>* < rmm@sqlisor.com
<mailto:rmm@sqlisor.com>> wrote:

Oracle Style packages on postgres

OVERVIEW:

To emulate oracle server side development in postgres I required server
side packages. The following text demonstrates how to do this using
plpython on postgres 8 and suggests a language extension.

WHAT ARE ORACLE PACKAGES?

Looking back over the postgres discussion forums (particulary a
discussion
in 2001 following a proposal by Bill Studenmund) there appears to be
some
confusion over what oracle packages are. Here's a concise definition :
"A black box processing engine with one or more public access
functions
that retains state across calls"
An oracle package is created when first referenced. Its initialization
code is run once (ie costly queries to populate session wide package
params) and the package dies at the end of the session
An analogy with OOP is that it's like having a single class instance
available for the duration of a session.

SOME POWERFUL USES OF PACKAGES:

1. Pipes - oracle dbms_pipe built-in allows asynchronous communication
between any number of producer/consumer database sessions on any
number of
pipes

2. Logging - leave all logging/debug statements in code, decision on
logging output can be made when the logging package is initialised
(eg by
querying lookup tables for user, on/off, level, and
destination). Combine
logging with pipes and the output can be stored in tables seperate from
the current transaction. Include timing info down to milliseconds and
live problems/bottlenecks can more easily be identified.

3. Batch reporting - more suited to autonomous transactions than logging
but useful to have the report package store start time, duration,
error/warning count running totals etc. and summarize automatically at
report end.

See the example below on how to implement a version of the oracle
dbms_output package in plpython

EXTENSIONS TO POSTGRES:

Oracle style package creation syntax is split into header and body
so that
the body(code) can be re-compiled without invalidating dependent
objects.
Postgres syntax for the dbms_output example (in any postgres server
side
language) would be along the lines of:
CREATE OR REPLACE PACKAGE HEADER dbms_output AS
FUNCTION dbms_output_put_line(text) RETURNS text,
FUNCTION dbms_output_get_lines() RETURNS text;
CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
<language>;

Adding pg_package with a link from pg_proc are the only changes required
to the data dictionary.
It would be nice to have similar dotted syntax as oracle
(user.package.function) but would this mess up postgres namespaces?

The language in which the package was created would process the 'package
code', for example in python:
o create public functions linking header declaration to package
body code
(see dbms_output example)
o process embedded sql, eg l_curs=select * from dual ->
l_curs=self.execute('select * from dual')
o the extracted sql can be 'prepared' by postgres and syntax exceptions
reported as compilation errors

SUMMARY:
Packages are an important addition to postgres. Some of the server side
languages have the potential to create them now. It would be useful to
add a common high level syntax before the various language
implementations
start developing their own solutions.

I'm currently testing dbms_pipe on postgres, let me know if anyone is
interested. I replaced xml-rpc (5 messages/second) by sockets (600x
faster!), and may test corba

Ronnie Mackay

-----------------------------------------------------------------------------
-----------------------------------------------------------------------------

EXAMPLE :CONVERT ORACLE BUILT-IN PACKAGE DBMS_OUTPUT:

[Oracle syntax is :exec dbms_output.put_line('line1');]

Postgres>select dbms_output_put_line('line 1');
Postgres>select test_call_dbms_output_from_within_plpgsql('line 2
(plpgsql)');
Postgres>select test_call_dbms_output_from_within_plpython('line 3
(plpython)');
Postgres>select dbms_output_put_line('line 4');

Postgres>select dbms_output_get_lines();
--- DBMS_OUTPUT DEMO ---
line 1
line 2 (plpgsql)
line 3 (plpython)
line 4
--- DBMS_OUTPUT DEMO ---

So using current postgres syntax the only difference with oracle is that
dbms_output.put_line('line 1'); becomes
dbms_output_put_line('line 1');
The source code to implement the package body is returned by postgres
function dbms_output()

POSTGRES CREATE STATEMENTS FOR EXAMPLE:
-----------------------------------------------------------------------------

CREATE or replace FUNCTION dbms_output_put_line(text) RETURNS text AS $$
from plpython import getPackage
return getPackage(GD, plpy, 'dbms_output').putLine(args[0])
$$ LANGUAGE plpythonu;

CREATE or replace FUNCTION dbms_output_get_lines() RETURNS text AS $$
from plpython import getPackage
return getPackage(GD, plpy, 'dbms_output').getLines()
$$ LANGUAGE plpythonu;

-- package body
CREATE OR REPLACE FUNCTION dbms_output() RETURNS text AS $$
return """
from plpython import PlPythonPackage

class Package(PlPythonPackage):

def __init__(self, in_plpy):

PlPythonPackage.__init__(self, in_plpy)
self.lines=[]

def putLine(self, in_text):
self.lines.append(in_text)

def getLines(self):
l_lines=self._title()
l_lines+=self.lines
l_lines+=self._title()
self.lines=[]
return chr(10).join(l_lines)

def _title(self):
return ['--- DBMS_OUTPUT DEMO ---']
"""
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION
test_call_dbms_output_from_within_plpython(in_text text) RETURNS
text AS
$$
from plpython import getPackage
dbms_output = getPackage(GD, plpy, 'dbms_output')
print dbms_output
print dir(dbms_output)
dbms_output.putLine(args[0])
$$ LANGUAGE plpythonu;

CREATE or replace FUNCTION
test_call_dbms_output_from_within_plpgsql(in_text text) RETURNS text
AS $$
declare
dummy text;
BEGIN
dummy := dbms_output_put_line(in_text);
return '';
END;
$$ LANGUAGE plpgsql;

-----------------------------------------------------------------------------

PYTHON MODULE (plpython.PlPythonPackage):
-----------------------------------------------------------------------------

import imp, sys

class PlPythonPackage:
""" Base class for postgres emulation of oracle package structure in
PlPython """

def __init__(self, in_plpy):
self.plpy=in_plpy
l_row=self.plpy.execute('select current_user as user,
current_database() as database')[0]
self.user=l_row ["user"]
self.database=l_row["database"]

def execute(self, in_sql):
l_result = self.plpy.execute(in_sql)

def getPackage(in_gd, in_plpy, in_package):
""" Dynamically load plpython package"""
try:
return in_gd[in_package]
except KeyError:
l_result=in_plpy.execute('select %s()'%in_package)
l_code=l_result[0].popitem()[1].replace('\n\t','\n')
l_module = imp.new_module (in_package)
exec l_code in l_module.__dict__
l_package=l_module.Package(in_plpy)
in_gd[in_package]=l_package
return l_package

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org <mailto:majordomo@postgresql.org>

--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/

Satoshi Nagayasu wrote:

An oracle package is created when first referenced. Its initialization
code is run once (ie costly queries to populate session wide package
params) and the package dies at the end of the session
An analogy with OOP is that it's like having a single class instance
available for the duration of a session.

PL/Java has an object called "Session" that does exactly this. It is not
available from other languages at present. Are Packages supposed to be
cross-language?

Regards,
Thomas Hallgren

On E, 2005-05-09 at 07:36 +0200, Thomas Hallgren wrote:

Satoshi Nagayasu wrote:

An oracle package is created when first referenced. Its initialization
code is run once (ie costly queries to populate session wide package
params) and the package dies at the end of the session
An analogy with OOP is that it's like having a single class instance
available for the duration of a session.

PL/Java has an object called "Session" that does exactly this.

And pl/python has a global dictionary SD for the same purpose.

It is not
available from other languages at present. Are Packages supposed to be
cross-language?

Probably not, as they already have most of the needed features.

Maybe we can set up some lighter version of package for cross-language
features (like installing removing a group of functions) but this are
much less needed for more advanced languages.

--
Hannu Krosing <hannu@skype.net>

On Sun, May 08, 2005 at 10:38:41PM -0500, Bob wrote:

One simple benefit to packages is just organization of related code.

Which, IMHO, is greatly diminished by the lack of
schema.package.function notation. BTW, the original post referred to
this as user.package.function, but I believe that technically it's
actually schema.package.function (Oracle tends to mix schemas and
users). In any case, schema.package.function is what would make sense in
PostgreSQL.

Personally, I think the biggest win here would be adding package support
and syntax to plpgsql. Not only would it make porting from Oracle
easier, it would also make plpgsql much, much more powerful.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

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

Rmm,

"A black box processing engine with one or more public access functions
that retains state across calls"

In other words, an Object. <grin>

Oracle style package creation syntax is split into header and body so that
the body(code) can be re-compiled without invalidating dependent objects.
Postgres syntax for the dbms_output example (in any postgres server side
language) would be along the lines of:
CREATE OR REPLACE PACKAGE HEADER dbms_output AS
FUNCTION dbms_output_put_line(text) RETURNS text,
FUNCTION dbms_output_get_lines() RETURNS text;
CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
<language>;

Hmmm. What about package variables? For me, this is one of the most
valuable parts of packages.

I've also never much liked Oracle's seperate package_header and package_body
declaration structure: if the two are intrinsically tied, why not make it one
declaration? Is syntactical compatibility important enough that we need to
imitate their design errors?

Adding pg_package with a link from pg_proc are the only changes required
to the data dictionary.
It would be nice to have similar dotted syntax as oracle
(user.package.function) but would this mess up postgres namespaces?

Yes, actually. If you look at the discussion, this is what killed the 2001
proposal; packages were proposed as orthagonal to schema which was not
acceptable.

However, now that schema are well established, it seems like this namespace
issue is limited. The problem would be that you'd have to make sure that no
two schema and packages had the same name, or that there would be an
automatic precedence of shema, package established.

So, given a shema named "dataloader" and a package named "dataloader" and a
function named "copy_it(filename)", what would happen is:

dataloader.dataloader.copy_it('/tmp/somefile')
... would be absolutely clear
dataloader.copy_it('/tmp/somefile')
... would attempt to call the copy_it function in the dataloader
*schema*, not the dataloader *package*.

The above seems inevitable, and not really a problem to me. We simply warn
people in the docs of the behavior, and to avoid duplicate naming.

I think there are more important questions:

1) how do you prevent users from executing the package functions outside of
the package?
2) Have you taken care of package variables? If so, are they only
per-session, or global? If they are global, how do you accomplish this?
3) For that matter, is initialization per session or global?

--
Josh Berkus
Aglio Database Solutions
San Francisco

I agree wholeheartedly and was actually just thinking of this yesterday.

Back when I was working on NEXTGRES I implemented package support into
plpgsql including scopes. While my time is pretty tight right now, I'd
be more than willing to work with whoever the plpgsql master is.

Jim C. Nasby wrote:

Hey everyone,

In addition to package support in plpgsql, it would be really handy to
have inline plpgsql. Likewise, I think there are others who feel this
way as-well.

Years ago, Oracle merged PL/SQL with their normal SQL parser which
allowed for inline PL/SQL. They did this because it was difficult to
maintain two separate parsers. While this worked great for Oracle, it
probably wouldn't really work as well for PostgreSQL because pgsql
supports multiple procedural languages.

As for implementation, I think it would obviously be best to leave
plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the
normal parser and have the system generate/execute a function on the
fly. Or, maybe it would be better to integrate plpgsql. Or, I may just
be crazy.

Would anyone else ever benefit from inline functions? Does anyone have
any ideas about implementation? Please shoot your opinions this way.
Thanks.

-Jonah

As for implementation, I think it would obviously be best to leave
plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the
normal parser and have the system generate/execute a function on the
fly. Or, maybe it would be better to integrate plpgsql. Or, I may just
be crazy.

Would anyone else ever benefit from inline functions?

Well I could see inline functions being useful for debugging a function
during development but I don't think I would want a bunch of plPGSQL
mucking up my pretty SQL :)

Sincerely,

Joshua D. Drake

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Jonah,

In addition to package support in plpgsql, it would be really handy to
have inline plpgsql. Likewise, I think there are others who feel this
way as-well.

I think a number of people would be interested in this. However, your
biggest development issue, as I've been told, is that the Pl/pgSQL parser
isn't nearly as mature as the SQL parser. So an overhaul of the PL/pgSQL
backend code would need to precede any merger of the two parsers.

Also, take a look at the "pl/pgsql enabled by default" thread on this mailing
list for security concerns. These security concerns would be much more
significant if plpgsql were automatically available on the command line. As
such, you'd need to make building it in to the SQL command line a
compile-time option so that security-conscious admins could disable it if
they want to.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Joshua D. Drake wrote:

As for implementation, I think it would obviously be best to leave
plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the
normal parser and have the system generate/execute a function on the
fly. Or, maybe it would be better to integrate plpgsql. Or, I may
just be crazy.

Would anyone else ever benefit from inline functions?

Well I could see inline functions being useful for debugging a
function during development but I don't think I would want a bunch of
plPGSQL mucking up my pretty SQL :)

Then don't put it there ;-)

I think you'd need to do something like this:

PERFORM language plpgsql $$

-- some plpgsql stuff here

$$;

cheers

andrew

On Mon, May 09, 2005 at 10:05:38AM -0700, Josh Berkus wrote:

I've also never much liked Oracle's seperate package_header and package_body
declaration structure: if the two are intrinsically tied, why not make it one
declaration? Is syntactical compatibility important enough that we need to
imitate their design errors?

Actually, there is a notable difference between the two. Replacing the
body of a package has a minimal impact on the database, but replacing
the header requires more work to invalidate cached stuff. I think
there's also a few other side effects.

This isn't to say that this is a good way to handle this, but I believe
it's why Oracle does it.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

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

I would be interested in hearing how we can implement Oracle packages in
a way that seamlessly integrates into what we have. Is it like
functions that are automatically called when a schema is accessed? And
the result put into a per-session temporary schema?

I think it is unlikely we would implement Oracle packages exactly like
Oracle but I think there is interest in adding that functionality to
PostgreSQL.

If we can work up a list I can add it to the TODO list.

---------------------------------------------------------------------------

Josh Berkus wrote:

Rmm,

"A black box processing engine with one or more public access functions
that retains state across calls"

In other words, an Object. <grin>

Oracle style package creation syntax is split into header and body so that
the body(code) can be re-compiled without invalidating dependent objects.
Postgres syntax for the dbms_output example (in any postgres server side
language) would be along the lines of:
CREATE OR REPLACE PACKAGE HEADER dbms_output AS
FUNCTION dbms_output_put_line(text) RETURNS text,
FUNCTION dbms_output_get_lines() RETURNS text;
CREATE OR REPLACE PACKAGE BODY dbms_output AS $$ 'package code' $$
<language>;

Hmmm. What about package variables? For me, this is one of the most
valuable parts of packages.

I've also never much liked Oracle's seperate package_header and package_body
declaration structure: if the two are intrinsically tied, why not make it one
declaration? Is syntactical compatibility important enough that we need to
imitate their design errors?

Adding pg_package with a link from pg_proc are the only changes required
to the data dictionary.
It would be nice to have similar dotted syntax as oracle
(user.package.function) but would this mess up postgres namespaces?

Yes, actually. If you look at the discussion, this is what killed the 2001
proposal; packages were proposed as orthagonal to schema which was not
acceptable.

However, now that schema are well established, it seems like this namespace
issue is limited. The problem would be that you'd have to make sure that no
two schema and packages had the same name, or that there would be an
automatic precedence of shema, package established.

So, given a shema named "dataloader" and a package named "dataloader" and a
function named "copy_it(filename)", what would happen is:

dataloader.dataloader.copy_it('/tmp/somefile')
... would be absolutely clear
dataloader.copy_it('/tmp/somefile')
... would attempt to call the copy_it function in the dataloader
*schema*, not the dataloader *package*.

The above seems inevitable, and not really a problem to me. We simply warn
people in the docs of the behavior, and to avoid duplicate naming.

I think there are more important questions:

1) how do you prevent users from executing the package functions outside of
the package?
2) Have you taken care of package variables? If so, are they only
per-session, or global? If they are global, how do you accomplish this?
3) For that matter, is initialization per session or global?

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

Josh Berkus <josh@agliodbs.com> writes:

Yes, actually. If you look at the discussion, this is what killed the 2001
proposal; packages were proposed as orthagonal to schema which was not
acceptable.

I think what actually killed that proposal was that it was not made
clear what it did that wouldn't be done as well (and in a more standard
fashion) by providing schemas.

What I read in this thread is that the only truly missing feature is
package variables (ie, session-local variables); is that an accurate
statement? If so, it would seem simplest to add such a feature to
plpgsql and be done with it. Several people already pointed out that
most of the other PLs support that feature today.

regards, tom lane

Tom,

What I read in this thread is that the only truly missing feature is
package variables (ie, session-local variables); is that an accurate
statement? If so, it would seem simplest to add such a feature to
plpgsql and be done with it. Several people already pointed out that
most of the other PLs support that feature today.

Also initialization, namespacing, and security. The ability to "package"
bunches of functions, and only allow their calling in the context of a
package, is quite valuable in installations which support 1,000's of
procedures.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

What I read in this thread is that the only truly missing feature is
package variables (ie, session-local variables); is that an accurate
statement? If so, it would seem simplest to add such a feature to
plpgsql and be done with it. Several people already pointed out that
most of the other PLs support that feature today.

Also initialization, namespacing, and security. The ability to "package"
bunches of functions, and only allow their calling in the context of a
package, is quite valuable in installations which support 1,000's of
procedures.

This is exactly the sort of argumentation that got the last proposal
shot down ;-). I see no reason that you can't do the namespacing and
security as well or better using the existing (and more standard) schema
feature. If there's something there that's not covered, what is it?

(The initialization bit goes along with the variables, AFAICS.)

regards, tom lane

On E, 2005-05-09 at 11:44 -0600, Jonah H. Harris wrote:

Hey everyone,

In addition to package support in plpgsql, it would be really handy to
have inline plpgsql. Likewise, I think there are others who feel this
way as-well.

Session variables is what I miss most.

Years ago, Oracle merged PL/SQL with their normal SQL parser which
allowed for inline PL/SQL.

Was that really that much time ago ? IIRC this was fanfared as one of
big advancements of Oracle 10.

They did this because it was difficult to maintain two separate parsers.

Also they claimed that this cleared away some subtle differences in the
languages supported by SQL and pl/SQL.

While this worked great for Oracle, it
probably wouldn't really work as well for PostgreSQL because pgsql
supports multiple procedural languages.

AFAIK Oracle also supports at least java, using a syntax somewhat
similar to ours.

As for implementation, I think it would obviously be best to leave
plpgsql on its own as a PL but maybe change BEGIN and DECLARE in the
normal parser and have the system generate/execute a function on the
fly. Or, maybe it would be better to integrate plpgsql. Or, I may just
be crazy.

Just having $$ quoting and named arguments does most of what I need for
using functions from a command line. If pl/pgsql and perhaps even plain
sql get session variables, preferrably usable by both (and in future
accessible from other) pl-s that would cover most of my needs.

Would anyone else ever benefit from inline functions? Does anyone have
any ideas about implementation? Please shoot your opinions this way.

While I can imagine how to use a declarative language from procedural
one (pl/pgsql using sql) I have much harder time to imagine how to do
the opposite in a convevient way.

something like this ? :

select $$ inline scalar plpgsql func here $$, count(*)
from $$ inline set function here $$ sub
group by 1;

perhaps just supporting TEMP funcions should be enough ?

--
Hannu Krosing <hannu@tm.ee>

Tom,

This is exactly the sort of argumentation that got the last proposal
shot down ;-). I see no reason that you can't do the namespacing and
security as well or better using the existing (and more standard) schema
feature. If there's something there that's not covered, what is it?

a) When you have 1000's of procedures, it becomes very useful to have more
than one level of namespacing. This is not an exaggeration; one project I
looked at who decided not to convert from Oracle to PostgreSQL had over
100,000 procedures and functions. Lack of packages was their main reason
for not switching. Schemas provide only *one* level of namespacing, unless
we want to "improve" on the SQL standard and allow nested schemas.

b) Schemas do not provide us with any way of limiting the scope of functions
and persistent variables. With packages, you would want:
1. functions which can only be called internally to the package
2. variables which are only visible inside the package
3. functions which can only be called as part of the package (thus utilizing
the initialization and internal variables) and not on their own.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, May 09, 2005 at 11:44:23AM -0600, Jonah H. Harris wrote:

Hey everyone,

In addition to package support in plpgsql, it would be really handy
to have inline plpgsql. Likewise, I think there are others who feel
this way as-well.

Why yes, there are. :)

Years ago, Oracle merged PL/SQL with their normal SQL parser which
allowed for inline PL/SQL. They did this because it was difficult
to maintain two separate parsers. While this worked great for
Oracle, it probably wouldn't really work as well for PostgreSQL
because pgsql supports multiple procedural languages.

I proposed a syntax for this awhile back. I haven't found it in the
archives, but it goes like this:

EXECUTE IMMEDIATE $$
function body here
$$
LANGUAGE plfoo;

Similarly, a CREATE TEMPORARY FUNCTION could be quite handy.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!