Packages: Again

On Wed, Jan 11, 2017 at 10:57:58AM -0800, Joshua Drake wrote:

-hackers,

I know we have talked about this before but today it was impressed upon me
rather firmly. I presented a Webinar: Postgres for Oracle People. The
attendees were 90% pl/pgsql developers. 330 people registered for an event
that was only allowed to host 100 people. The webinar went on for 2 hours.
(it was only scheduled for one hour, that is how interactive it was)

By far the tagline of this webinar from attendees was, "We can not port
without packages"

So this is a reality. If we want tried and true Oracle developers to port to
PostgreSQL, we must provide some level of package capability.

There are some that would say we don't need them. You are right, we don't
need them. We should however want them if we want to continue to stomp
through the business sector and continue growth.

I use this post to inspire conversation on how we can get this done.

So, we have a TODO item with links:

Add features of Oracle-style packages

A package would be a schema with session-local variables,
public/private functions, and initialization functions. It is also
possible to implement these capabilities in any schema and not use a
separate "packages" syntax at all.

proposal for PL packages for 8.3.
proposal: schema PL session variables
proposal: session server side variables

Is there anything that needs updating there, or it is just a question of
getting someone to implement it?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2017-01-11 19:57 GMT+01:00 Joshua D. Drake <jd@commandprompt.com>:

-hackers,

I know we have talked about this before but today it was impressed upon me
rather firmly. I presented a Webinar: Postgres for Oracle People. The
attendees were 90% pl/pgsql developers. 330 people registered for an event
that was only allowed to host 100 people. The webinar went on for 2 hours.
(it was only scheduled for one hour, that is how interactive it was)

By far the tagline of this webinar from attendees was, "We can not port
without packages"

So this is a reality. If we want tried and true Oracle developers to port
to PostgreSQL, we must provide some level of package capability.

There are some that would say we don't need them. You are right, we don't
need them. We should however want them if we want to continue to stomp
through the business sector and continue growth.

We have a schemas instead - the PostgreSQL schema is close to Oracle
packages.

What we cannot to substitute are package variables, now - see my proposal
for session variables.

Now I am working on migration some large Oracle project - I see more
significant issues

1. no good tools - ora2pg do lot of work, but the PL/SQL -> PL/pgSQL
migration support is basic
2. some things in Postgres are different - boolean type, enum types, date
type, OUT parameters ..
3. some things are really different - NULL versus empty string
4. there are not good tools for postprocessing PL/pgSQL beautifier
(formatter), SQL formatter
5. The developers still using Oracle outer joins - there are not 100%
automatic migration
6. missing some common patterns for deployment, tests for really big set of
code.

Now I work on migration about 500K rows - and it is terrible work. It is 20
years old project - lot of code is not clean, It is hard to migrate, it is
hard to clean. Sure, there is not one line of tests.

If we miss some, then it is modern robust tool for migration - big thanks
to ora2pg maintainers and developers - without it, there is nothing free.

Regards

Pavel

On Wed, Jan 11, 2017 at 08:32:53PM +0100, Pavel Stehule wrote:

Now I work on migration about 500K rows - and it is terrible work. It is 20
years old project - lot of code is not clean, It is hard to migrate, it is hard
to clean. Sure, there is not one line of tests.

If we miss some, then it is modern robust tool for migration - big thanks to
ora2pg maintainers and developers - without it, there is nothing free.

I think we need to focus on things that _can't_ be done first, rather
than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it. It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

We have a schemas instead - the PostgreSQL schema is close to Oracle
packages.

Yes, a schema is a kind of a "namespace"-level package. Pg also has
extensions, which is a group things put together, which may also
contribute to packaging.

What we cannot to substitute are package variables, now - see my proposal
for session variables.

I would like also to point out here that Pg has dynamic text session
variables with a horrible syntax, aka user-defined GUCs. They can be the
basis for more useful variables if extended with privacy/some access
control, typing, better syntax, possibly some kind of persistent
declarations, and so on.

[...]

Good luck with your migration...

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2017-01-11 20:42 GMT+01:00 Bruce Momjian <bruce@momjian.us>:

On Wed, Jan 11, 2017 at 08:32:53PM +0100, Pavel Stehule wrote:

Now I work on migration about 500K rows - and it is terrible work. It is

20

years old project - lot of code is not clean, It is hard to migrate, it

is hard

to clean. Sure, there is not one line of tests.

If we miss some, then it is modern robust tool for migration - big

thanks to

ora2pg maintainers and developers - without it, there is nothing free.

I think we need to focus on things that _can't_ be done first, rather
than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it. It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

There are some workarounds for emulation of package variables - and I am
pressing a design of session variables that can be well used like package
variables.

Currently almost all basic functionality (related to PL/SQL) is available
in Postgres. But the migration needs lot of hard manual work.

It is analogy with Cobol systems - some applications are too ugly so more
level emulation is cheaper solution, than migration to some modern :)

Regards

Pavel

I think we need to focus on things that _can't_ be done first, rather
than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it. It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

Nested/autonomous transactions? Do they occur often in PL/SQL code?

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Fabien,

* Fabien COELHO (coelho@cri.ensmp.fr) wrote:

I think we need to focus on things that _can't_ be done first, rather
than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it. It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

Nested/autonomous transactions? Do they occur often in PL/SQL code?

Yes.

Thanks!

Stephen

On Wed, Jan 11, 2017 at 08:56:23PM +0100, Fabien COELHO wrote:

I think we need to focus on things that _can't_ be done first, rather
than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it. It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

Nested/autonomous transactions? Do they occur often in PL/SQL code?

Yes, they do based on the number of "I can't port from Oracle"
complaints we used to get, perhaps related to exceptions. Once we had
them, the complaints of that type disappeared.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2017-01-11 20:56 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

I think we need to focus on things that _can't_ be done first, rather

than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it. It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

Nested/autonomous transactions? Do they occur often in PL/SQL code?

There is relative well working workaround - ora2pg is able to translate it
to dblink usage.

Sure - native solution can be better - usage pg_background is step forward.

Regards

Pavel

On Wed, Jan 11, 2017 at 03:08:58PM -0500, Bruce Momjian wrote:

On Wed, Jan 11, 2017 at 08:56:23PM +0100, Fabien COELHO wrote:

I think we need to focus on things that _can't_ be done first, rather
than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it. It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

Nested/autonomous transactions? Do they occur often in PL/SQL code?

Yes, they do based on the number of "I can't port from Oracle"
complaints we used to get, perhaps related to exceptions. Once we had
them, the complaints of that type disappeared.

Oh, I was talking about savepoints/nested-transactions, not autonomous
transactions.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2017-01-11 21:08 GMT+01:00 Bruce Momjian <bruce@momjian.us>:

On Wed, Jan 11, 2017 at 08:56:23PM +0100, Fabien COELHO wrote:

I think we need to focus on things that _can't_ be done first, rather
than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it. It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

Nested/autonomous transactions? Do they occur often in PL/SQL code?

Yes, they do based on the number of "I can't port from Oracle"
complaints we used to get, perhaps related to exceptions. Once we had
them, the complaints of that type disappeared.

We have not PL controllable transactions - so some patterns are not
available in our functions.

On second hand - currently all usage of explicit commit/rollback was
related to some Oracle issue (what I know)

1. missing easy debug printing - there was not nothing like RAISE NOTICE -
dbms_output - is poor solution

2. it was workaround for limited transaction size

In 90% it are solutions of issues that are not in Postgres. Can be nice to
have procedures - and it can be benefit for all, but it is not too big gap.
When you use postgres's patterns, then you don't need it - but there are
more work with migration.

Regards

Pavel

On 01/11/2017 11:32 AM, Pavel Stehule wrote:

We have a schemas instead - the PostgreSQL schema is close to Oracle
packages.

No. It isn't.

A Package is essentially a class with dependencies. It has nothing to do
with schemas outside of being named qualified. For example:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/packages.htm#i4362

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jan 11, 2017 at 3:56 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

We have a schemas instead - the PostgreSQL schema is close to Oracle
packages.

No. It isn't.

I'm gonna say "yeah, it is".

And that's all I will say about this topic.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Le 11/01/2017 à 20:32, Pavel Stehule a écrit :

2017-01-11 19:57 GMT+01:00 Joshua D. Drake <jd@commandprompt.com
<mailto:jd@commandprompt.com>>:

-hackers,

I know we have talked about this before but today it was impressed
upon me rather firmly. I presented a Webinar: Postgres for Oracle
People. The attendees were 90% pl/pgsql developers. 330 people
registered for an event that was only allowed to host 100 people.
The webinar went on for 2 hours. (it was only scheduled for one
hour, that is how interactive it was)

By far the tagline of this webinar from attendees was, "We can not
port without packages"

So this is a reality. If we want tried and true Oracle developers
to port to PostgreSQL, we must provide some level of package
capability.

There are some that would say we don't need them. You are right,
we don't need them. We should however want them if we want to
continue to stomp through the business sector and continue growth.

We have a schemas instead - the PostgreSQL schema is close to Oracle
packages.

What we cannot to substitute are package variables, now - see my
proposal for session variables.

Now I am working on migration some large Oracle project - I see more
significant issues

1. no good tools - ora2pg do lot of work, but the PL/SQL -> PL/pgSQL
migration support is basic
2. some things in Postgres are different - boolean type, enum types,
date type, OUT parameters ..
3. some things are really different - NULL versus empty string
4. there are not good tools for postprocessing PL/pgSQL beautifier
(formatter), SQL formatter
5. The developers still using Oracle outer joins - there are not 100%
automatic migration
6. missing some common patterns for deployment, tests for really big
set of code.

Now I work on migration about 500K rows - and it is terrible work. It
is 20 years old project - lot of code is not clean, It is hard to
migrate, it is hard to clean. Sure, there is not one line of tests.

If we miss some, then it is modern robust tool for migration - big
thanks to ora2pg maintainers and developers - without it, there is
nothing free.

Regards

Pavel

Hi,

I'm currently working on release 19.0 of Ora2Pg, I hope to get it out
this weekend. This release has a major rewrite of the pl/psql rewriter.
Some of the issues you've reported to me Pavel are already solved in
this branch, some other have been fixed after your reports. The rewriter
has no more limitation in rewriting function call like decode(),
previous version was failing to rewrite function call when an other
function or sub select was called inside.

Ora2Pg has always used schema to replace package and I think it is the
good equivalent to Oracle's package, we don't need more. The only thing
that is missing are global variables. Release 19.0 of Ora2Pg will try to
address this problem by exporting global variables declared into the
package as PostgreSQL user defined custom variable and replace all call
to these variables in the plpgsql code by

current_setting('schema_name.var_name')::var_type

and all affectation of these variables by

SELECT / PERFORM set_config('schema_name.var_name', var_value, false);

This works great but the only difference with Oracle's global variables
is that they are visible outside the schema where, in Oracle, they are
only visible in the package scope when declared in the package body.
Perhaps we can work on having these custom variables visible only in a
particular schema or some other mechanism that made them accessible from
the plpgsql code only. Ora2Pg doesn't propose any solution to cursors
declared as global variable yet.

Ora2Pg can fully rewrite Oracle's function with autonomous transaction
using a wrapper with a call to dblink or pg_background if you enable it.
It's just works perfectly.

About Oracle's OUTER JOIN notation, (+), next release of Ora2Pg will be
able to rewrite simple form of RIGHT OUTER JOIN, LEFT OUTER JOIN will
comes soon. Note that there is no automatic tool to rewrite the Oracle
outer join syntax, but you can use TOAD to convert the queries into ANSI
syntax unfortunately query per query and manually. Next version will
also add better support to export Oracle Text Search indexes using
pg_trgm, unaccent and FTS.

Including a SQL and plpgsql code beautifier is also in my todo list,
probably available in next major version 20.

In my opinion, Ora2Pg can do more automatic works but it need some more
developments. I would like to give all my time to improve this project
and give you a better tool but this is not really possible for the
moment and unfortunately my spare time is not extensible. I'm doing my
best to get out more releases, your reports/feedbacks help me a lot to
add more automatic migration code. I don't think it is possible to have
a 100% automatic migration because there will always be some things that
need manual rewrite, like point 3, I don't think we want stuff like NULL
equal EMPTY. There is also tons of external modules like DBMS_* that can
be compared to extension, but if every one share is work on migration
perhaps we can save more of time.

Regards,

--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org

On 12 Jan. 2017 02:59, "Joshua D. Drake" <jd@commandprompt.com> wrote:

-hackers,

I know we have talked about this before but today it was impressed upon me
rather firmly. I presented a Webinar: Postgres for Oracle People. The
attendees were 90% pl/pgsql developers. 330 people registered for an event
that was only allowed to host 100 people. The webinar went on for 2 hours.
(it was only scheduled for one hour, that is how interactive it was)

By far the tagline of this webinar from attendees was, "We can not port
without packages"

What aspects / features of packages were the key issues?

Initialisation?

Variables?

Performance features like pre compilation?

Signing?

Code obfuscation?

...?

So this is a reality. If we want tried and true Oracle developers to port
to PostgreSQL, we must provide some level of package capability.

There are some that would say we don't need them. You are right, we don't
need them. We should however want them if we want to continue to stomp
through the business sector and continue growth.

I use this post to inspire conversation on how we can get this done.

Sincerely,

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/11/2017 04:12 PM, Craig Ringer wrote:

What aspects / features of packages were the key issues?

Unfortunately we didn't get too far into it because the webinar was
about Postgres specifically. That said, I have been doing some followup.
Here is some of it:

because packages[1]

o break the dependency chain (no cascading invalidations when you
install a new package body -- if you have procedures that call
procedures -- compiling one will invalidate your database)

o support encapsulation -- I will be allowed to write MODULAR, easy to
understand code -- rather then MONOLITHIC, non-understandable procedures

o increase my namespace measurably. package names have to be unique in a
schema, but I can have many procedures across packages with the same
name without colliding

o support overloading

o support session variables when you need them

o promote overall good coding techniques, stuff that lets you write code
that is modular, understandable, logically grouped together....

Note: I am not arguing the technical merits here. My goal is 100%, how
do we get Oracle folks a true Open Source Oracle alternative.

As I get more from people, I will post.

Sincerely,

JD

1.
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7452431376537

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 13 Jan. 2017 00:54, "Joshua D. Drake" <jd@commandprompt.com> wrote:

On 01/11/2017 04:12 PM, Craig Ringer wrote:

What aspects / features of packages were the key issues?

Unfortunately we didn't get too far into it because the webinar was about
Postgres specifically. That said, I have been doing some followup. Here is
some of it:

because packages[1]

o break the dependency chain (no cascading invalidations when you install a
new package body -- if you have procedures that call procedures --
compiling one will invalidate your database)

o support encapsulation -- I will be allowed to write MODULAR, easy to
understand code -- rather then MONOLITHIC, non-understandable procedures

o increase my namespace measurably. package names have to be unique in a
schema, but I can have many procedures across packages with the same name
without colliding

o support overloading

o support session variables when you need them

o promote overall good coding techniques, stuff that lets you write code
that is modular, understandable, logically grouped together....

So far that's all "that'd be nice, but isn't a technical barrier" stuff.

Package variables for example. When _do_ you _need_ them? For what? (I'm
aware of some uses but "when you need them" helps us not at all).

On 01/12/2017 03:35 PM, Craig Ringer wrote:

So far that's all "that'd be nice, but isn't a technical barrier" stuff.

Package variables for example. When _do_ you _need_ them? For what? (I'm
aware of some uses but "when you need them" helps us not at all).

Well my answer would be, "because we want Oracle people to have an easy
time migrating". I know that isn't the -hackers answer but clearly there
is a demand for them. I would note that EDB Advanced server supports
them, exactly because there is a demand for them.

Again, I am not making the technical argument here. I don't have the
time to research it. I am making a usability argument for a potentially
huge portion of database users to allow PostgreSQL to be more attractive
to them, argument.

I also received this today:

"""
Well, packages make programming much easier. Not only do you keep
related procedures together, you can also have private package variables
and the package initialization. Also, packages are units of security, so
you can grant permissions on the package to the entire group of users
and if you later modify the package and add a function, you don't need
to grant it separately.
"""

Sincerely,

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2017-01-13 3:07 GMT+01:00 Joshua D. Drake <jd@commandprompt.com>:

On 01/12/2017 03:35 PM, Craig Ringer wrote:

So far that's all "that'd be nice, but isn't a technical barrier" stuff.

Package variables for example. When _do_ you _need_ them? For what? (I'm
aware of some uses but "when you need them" helps us not at all).

Well my answer would be, "because we want Oracle people to have an easy
time migrating". I know that isn't the -hackers answer but clearly there is
a demand for them. I would note that EDB Advanced server supports them,
exactly because there is a demand for them.

EDB try to emulate Oracle - so some special features are necessary there -
although are redundant to existing PostgreSQL features - packages,
collections, ..

Again, I am not making the technical argument here. I don't have the time
to research it. I am making a usability argument for a potentially huge
portion of database users to allow PostgreSQL to be more attractive to
them, argument.

I also received this today:

"""
Well, packages make programming much easier. Not only do you keep related
procedures together, you can also have private package variables and the
package initialization. Also, packages are units of security, so you can
grant permissions on the package to the entire group of users and if you
later modify the package and add a function, you don't need to grant it
separately.
"""

This is possible with our schemas too. I use schemas for package emulation
for Orafce 10 years, and it is working well.

The main problem is in different languages - our "database" <> Oracle
"database", our "schema" <> Oracle "schema"

People doesn't want packages - they want Oracle without any fee