Re: PL/pgSQL 2

On Tue, Sep 2, 2014 at 5:47 PM, Álvaro Hernández Tortosa <aht@nosys.es> wrote:
>     Yeah, we differ there. I think having an Oracle compatibility layer in
> PostgreSQL would be the-next-big-thing we could have. Oracle is has orders
> of magnitude bigger user base than postgres has; and having the ability to
> attract them would bring us many many more users which, in turn, would
> benefit us all very significantly.
>
>     It would be my #1 priority to do in postgres (but yes, I know -guess-
> how hard and what resources that would require). But dreaming is free :)

There are a number of reasons why this isn't really practical.

First, Oracle compatibility isn't one feature.  The compatibility
"layer" that exists in EnterpriseDB's Advanced Server product consists
of many different changes to many different parts of the system.  A
few of those changes are simple syntax compatibility, where we do the
exact same thing PostgreSQL does but with different syntax, but a lot
of them are functional enhancements.  Even within SPL, there's a whole
bunch of different changes to a whole bunch of different areas, and
most of those are functional enhancements rather than just tweaking
syntax.  So, if you tried to implement a new, Oracle-compatible PL,
you'd find that you don't have one or a small number of changes to
make, but a long series of features ranging from small to very large.
You'd also find that adding a new PL, without changing any other parts
of the server, only bridges a small part of the compatibility gap.

Second, if you did manage to develop something which was significantly
more compatible with Oracle than PostgreSQL or PL/pgsql is today,
you'd probably find that the community wouldn't accept it.  It's
almost misleading to think of Oracle as a database; it's an enormous
software ecosystem with facilities for doing just about everything
under the sun, and many of those things more than one way.  For
example, in 9.4, EnterpriseDB will be releasing a UTL_HTTP package
that contains many of the same interfaces that are present in Oracle.
The interface decisions made by Oracle Corporation are reasonable in
view of their architecture, but I am quite sure that this community
would not want, for example, to return long text values as SETOF
VARCHAR(2000) rather than TEXT, just because Oracle does that.  And
rightly so: I wouldn't want PostgreSQL to follow any other product
that slavishly whether I worked at EnterpriseDB or not.  This kind of
thing crops up over and over again, and it only works to say that
PostgreSQL should choose the Oracle behavior in every case if you
believe that the primary mission of PostgreSQL should be to copy
Oracle, and I don't.  I also don't think it's a bad thing that
Advanced Server makes different decisions than PostgreSQL in some
cases.  A further problem is that, in this particular case, you'd
probably here the argument from PostgreSQL hackers that they really
don't want to be burdened with maintaining an HTTP client in the core
server when the same thing could be done from an extension, and that's
a valid argument, too.  It is also valid for EnterpriseDB to make a
different decision for itself, based on business priorities.

Now, none of that is to say that we wouldn't do well to give a little
more thought to Oracle compatibility than we do.  We've either made or
narrowly avoided a number of decisions over the years which introduced
- or threatened to introduce - minor, pointless incompatibilities with
other database products, Oracle included.  That really doesn't benefit
anyone.  To take another example, I've been complaining about the fact
that PostgreSQL 8.3+ requires far more typecasts in stored procedures
than any other database I'm aware of for years, probably since before
I joined EnterpriseDB.  And I still think we're kidding ourselves to
think that we've got that right when nobody else is doing something
similar.  I don't think the community should reverse that decision to
benefit EnterpriseDB, or to be compatible with Oracle: I think the
community should reverse that decision because it's stupid, and the
precedent of other systems demonstrates that it is possible to do
better.  Oracle's handling of reserved words also seems to be
considerably less irritating than ours, and I'd propose that we
improve that in PostgreSQL too, if I knew how to do it.
Unfortunately, I suspect that requires jettisoning bison and rolling
our own parser generator, and it's hard to argue that would be a good
investment of effort for the benefit we'd get.

Anyway, to get back around to the topic of PL/SQL compatibility
specifically, if you care about that issue, pick one thing that exists
in PL/SQL but not in PL/pgsql and try to do something about it.  Maybe
it'll be something that EnterpiseDB has already done something about,
in which case, if your patch gets committed, Advanced Server will lose
a bit of distinction as compared with PostgreSQL.  Or maybe it'll be
something that EnterpriseDB hasn't done anything about, and then
everybody comes out strictly ahead.  What I think you shouldn't do
(although you're free to ignore me) is continue thinking of Oracle
compatibility as one monolithic thing, because it isn't, or to pursue
of a course of trying to get the PostgreSQL community to slavishly
follow Oracle, because I think you'll fail, and even if you succeed I
don't think the results will actually be positive for PostgreSQL.

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