Re: PL/pgSQL 2

On 03/09/14 20:48, Robert Haas wrote:
> On Tue, Sep 2, 2014 at 5:47 PM, Álvaro Hernández Tortosa <aht(at)nosys(dot)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.

Hi Robert, thanks for the insights here. Understood it is not a
single thing the compatibility layer. And it's sure a very long and
involved task to build such compatibility parts.

However, I don't see anything bad in having one or some parts of
it. For example, having a pl that is similar -maybe only syntax- is a
good thing. Sure, there are surely lot of things that can't be done
simply, tons of functions not available and so on, but that alone would
mean Oracle users would feel both more comfortable and making their
current code easier to port. That would already be a lot.

>
> 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.

I wouldn't follow those routes just for doing perfect
compatibility. I agree, and I'd never push for those. In the light of
all these things, I'd never expect perfect, "scientific" compatibility,
but a best, but well documented, effort.

>
> 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.

I can't of course know for EDB, but in that case EDB would also
have a less distant fork to maintain, which isn't probably bad ;P

> 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.

As I said, I'd never pretend postgres to follow slavishly Oracle or
any other database. However, what I know is that there is a huge base
that would be willing to migrate from Oracle and they find it very hard
to do it with PostgreSQL. Any help in this direction would be very very
positive for postgres. And I'm sure there is lot to do without having
postgres become a slave of any other system. It's just about helping
(prospective) users.

Thank you for your insights,

Álvaro