Re: [HACKERS] Packages: Again

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

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