Hello group,

we are facing the task of migrating a large (600k LOC) oracle backend to
postgresql. As i figured out by now is that we theoretically have two
options:

1. teach Postgres to understand PL/SQL by using 3rd-party-software
2. convert oracle PL/SQL to PG PL/SQL

Has anyone of you already experience with such a job?
Where are problems to be expected?
Are there some tools for automated translating oracle PL/SQL to PG
PL/SQL? How good are they?
Are there other options except the two above?
What option is to be preferred?

.....and many, many more...

Thank you in advance.

Thomas

On Mon, 20 Jun 2011 19:28:45 +0200, Username wrote:

I've had such a pilot project but was forced to give it up. There are
several major snags with Postgres:
1) Postgres is the only major database without hints. That makes
keeping any deadlines impossible and makes it necessary to rewrite
each and every SQL. Tuning SQL in Postgres is done by rewriting it
and fixing the model, which takes time, in my case more time than
I was alloted. They do have "set mode" statements for the session,
but that's in effect for the entire session. Granularity is wrong.

2) Postgres partitioning is not very robust. It's even weaker than
Oracle7 partition views because partition elimination only happens
during the parse phase. Once the statement is parsed, that's it.
Did I mention that there are no global indexes? Also, optimizer seems
to be confused with partitioning and is frequently producing bad plans,
based on the full table scan of the entire table.

3) The most important problem are the people leading the community. They
stubbornly refuse to even talk about hints. Also, there is a closed
source version of Postgres, called EnterpriseDB which has hints. One
of the pillars of Postgres community is a guy who works for
EnterpriseDB and is opposed to hints in the open source version but is
merrily selling them in the closed source version. Here is a pearl of
wisdom, contributed to this valley of tears by another shining pillar
of the Postgres community: http://tinyurl.com/68gu822
To dispel any doubts, I am the DBA that this genius is talking about.

4) Postgres optimizer is bad. Statistics anomalies are frequent, there is
an email list devoted to Postgres performance and every other question
is "how to speed up this query". There is no tuning methodology, no
event interface, nothing.
5) There is no parallelism. Oracle can parallelize queries, Postgres can
not. It's as simple as that. There is no cure in sight.

There are some other problems, like not caching parsed SQL (no shared
pool) but these were sufficient to cast serious doubt on Postgres as a
viable option. I am an Oracle DBA for more than 20 years and recent
changes in the pricing policy have motivated the company that I work for
to look for alternatives, starting with OSS software. Sadly, there are no
open source alternatives. The spirit in the Postgres community ensures
that things will remain as they are, for the foreseeable future. The next
step is to look for the commercial alternatives, like DB/2. However, if
you are building a data warehouse, take a look at MongoDB. It's a NoSQL
database which can be used to create a very decent data warehouse.
If you want to rewrite Oracle code and use it on Postgres, forget it.
It's going to be a frustrating and humiliating experience and will
produce an underperforming application system. You can contact me
directly, should you need any more information.




--
http://mgogala.byethost5.com

Hello Mladen,

thank you for your detailed response.

Hmm. That's just *not* the answer was hoping to hear - but the one i
expected.

The "hint-discussion" is not new to me. I am already following it for
some time.

OK. Good to know. But that would not be the show stopper in my case.

Would EnterpriseDB be a better destination to migrate to from oracle?

Let's assume that this would not be a problem to me.

Also good to know. But not critically to my szenario.

That's exactly the situation that i am facing now.

Most of your critics on postgres refer to large amounts of data and
perfomance critical applications. In my scenario the oracle-db neither
uses hints or partitioned tables nor does performance matter that much.
I "only" have the problem of porting the 600k LOC PL/SQL application
logic (triggers, strored procedures etc.) to postgres.

Leaving your legitimate criticism on performance issues aside, and only
focussing on the pl/sql code, would you then still stay with your
introductory statement that it is impossible to migrate the code?

Thomas

On Mon, 20 Jun 2011 23:45:38 +0200, Username wrote:

If that is the case, I would try mixing PlPgSQ with PLPerl. PlPgSQL is
far inferior to PL/SQL. No packages, no procedures, but with Perl you can
get it all. With the untrusted version, you can call external modules.
Before you start: RAISE NOTICE is the PLPgSQL equivalent of
dbms_output.put_line.





--
http://mgogala.byethost5.com

On Mon, 20 Jun 2011 23:45:38 +0200, Username wrote:


That depends. It definitely has more useful goodies than the normal
Postgres, but I am not sure how stable the company is. They didn't make a
splash, EnterpriseDB is not becoming wildly popular, which is rather
strange, given how unhappy people are with the Oracle pricing. I don't
really know what seems to be the problem, but I am a DBA, not a business
person.



--
http://mgogala.byethost5.com

Username wrote:
I think that option 1 won't work.
You can look at EnterpriseDB, which is a closed source database based
on PostgreSQL that tries to be as similar to PostgreSQL as possible in
order to facilitate migration, but don't expect that you can just
go on using your PL/SQL procedures without modification.
Ask them, they'll be happy to tell you more.

Option 2 might or might not be feasible.

The syntax of PL/pgSQL is similar enough to PL/SQL that the code
itself won't be hard to port. You'll run into problems as soon as
you make heavy use of Oracle's goodies like the extensive PL/SQL
library, the LOB interface, autonomous transactions, bulk operations,
the object oriented cruft -- to name just a few of the more common
things that I can think of right away.

If your code uses these a lot, porting will be pretty difficult and
will probably end up in a rewrite. Note also that, different from PL/SQL,
PL/pgSQL is not designed to be an efficient all-purpose programming
language, it's designed for database manipulations where SQL won't suffice.
If you need to access the file system or other things outside the database,
it's better to use other languages like PL/Perl.

On the other hand, if your code is e.g. mostly triggers that modify
tables, porting might not be too difficult.

Yours,
Laurenz Albe

PS: Don't let people discourage you. PostgreSQL is a good and stable
database system.

On Tue, 21 Jun 2011 09:18:07 +0200, Laurenz Albe wrote:

It is stable, no contest there. Is it good or not is a matter of opinion.
I could list several grave faults which make it less than good in my eyes.
The first and foremost is, of course, the lack of hints. Postgres is the
only major database system which lacks hints. The second is ludicrous
need to "vacuum" database. The third is the fact that Postgres doesn't do
multi-block reads. Fixed size archive logs (16M) would be the next issue.
The lack of shared pool makes it impossible to see the plan of the
statement being executed. And there is more.


--
http://mgogala.byethost5.com

Mladen Gogala wrote:
You begin to sound like a broken record.
There are pros and cons to hints, and you can like them or not, but
from what you write it seems to be more of an emotional issue than anything
else, so I won't enter a discussion here.

A liberal use of pejoratives is no substitute for a technical argument.

I understand that with your Oracle background it is easy to think
that there is only one way to skin the cat, and other approaches are
"ludicrous".

Please don't turn into a troll.

Yours,
Laurenz Albe

PS: I have a question concerning Oracle's superior query optimizer.
I am dinking around with a "Foreign Data Wrapper" for the PostgreSQL 9.1
implementation of SQL/MED to access Oracle. Now it would be nice
to get optimizer information from Oracle and feed it back to PostgreSQL
so that it can be used for planing a query with a foreign table.

Is there any way to get a decent estimate how expensive Oracle thinks
a query might be? Something that can be expressed in units like blocks read
from disk or time spent? All I can find is the TIME column of PLAN_TABLE
which has a granularity of seconds (!) and is never less than 1.

Laurenz Albe, 22.06.2011 12:30:
He's been in my killfile since about 1 year...

On Wed, 22 Jun 2011 12:30:15 +0200, Laurenz Albe wrote:

Of course. There is no change in the situation.

All other major databases have them. That should be enough for now.

So what is this post all about, if you don't want to enter discussion?




--
http://mgogala.byethost5.com