Re: Packages and inner subprograms for PL/pgSQL

>> Thread start URL: https://www.postgresql.org/message-id/48A5C07D-6A66-4B96-A48C-75C530CB9DCF@yugabyte.com
>> On 16-Dec-2021, bryn(at)yugabyte(dot)com wrote:
>>
>> Folks who develop applications for Oracle Database have had the features that the subject line of this email lists since the arrival of PL/SQL in the early nineties. The advantages are self-evident to these programmers; and their lack comes as a shocking disappointment when they start to write application code for PostgreSQL*. The absence of packages and inner subprograms is huge. The absence of parameterizable anonymous blocks is a smaller limitation.
>>
>> Notice that this point is entirely separable from the endeavor of migrating an extant application. It has first and foremost to do with how you think of designing code.
>>
>> I’ve heard rumors that some contributors to the PostgreSQL implementation are interested in bringing the PL/pgSQL features that I mentioned. If there is any such thinking, please let me know. I’m not a C coder but I’d be very interested in reading any ordinary prose that describes how these features might be exposed to the PostgreSQL application developer.
>> ________________________________________________________________________________
>>
>> * Full disclosure: I was the product manager for PL/SQL, working at Oracle HQ, from about 2000 through 2019 when I started with Yugabyte Inc. At least some people on this list have heard of YugabyteDB and know that it uses Postgres’s SQL processing code “as is” (currently Version 11.2, but presently Version 13) on top of its own implementation of a distributed storage layer (inspired by Google Spanner).
>
> …
>
> On 23-Dec-2021, pavel(dot)stehule(at)gmail(dot)com wrote:
>
> I am sure packages have some advantages — this is an important feature of ADA language. The possibility of private objects is important and interesting. Possibility to sharing code is interesting too.
>
> But Postgres already has schemas (a little bit different from Oracle) and extensions. And internal implementation of PL/pgSQL disallow any sharing across databases. So introduction of packages to Postgres is introducing some not trivial and partially redundant concept. Currently, Postgres is relatively small and very very consistent software — and I believe so is one of the reasons why Postgres is popular. It is easy to learn, easy to use. The internal complexity is well solved and hidden. This is a long goal for community Postgres. The compatibility with Oracle should not be important after 20 years (although it is very important for a lot of current users and for users who can leave Oracle). If we miss some feature in Postgres, we should to implement it, but with respect to current features.

My message, sent six weeks ago, started a thread. I believe that Pavel’s message, sent about a week later, is the most recent. In this turn, I’ve tried to copy everybody who contributed to the thread.

In one of the turns, I promised a proper write-up of my case for PL/pgSQL packages. It took me some time because of the usual reasons (the Holiday period and ordinary work). It’s done now—attached as case-for-plpgsql-packages.zip. I decided to exclude the “parameterizable anonymous blocks” part of what I wrote to start this thread from my write-up. It’s an entirely separable notion. I allowed myself to change the “Subject” of this reply to reflect this..

The .zip contains these files:

case-for-plpgsql-packages.pdf — the prose write-up. It describes a use-case that is nicely implemented using a package (and package state). It presents the elided code of a working PL/SQL implementation. It transliterates this code naïvely into a strawman PL/pgSQL package. And it presents runnable the code that emulates the package like the PG doc section “ Porting from Oracle PL/SQL” recommends (except that I use a user-defined run-time parameter rather than a temporary table for the state.) The complete, non-ellided code is provided in the following .sql files.

orcl----run-me-in-Oracle-Database.sql
strawman-pg----pseudocode-cannot-be-run.sql
runnable-pg----run-me-in-PostgreSQL.sql

I’d be delighted to hear suggestions for a better runnable PL/pgSQL implementation—and happy to revise my code and write-up to use this. I’d also welcome general feedback (ordinarily in email, of course) and I’d be happy to revise my work and make a new .zip.

Finally, here are snippets from some of the responses. I hope that my essay, taken in its entirety, addresses all of these questions.

pg(at)bowt(dot)ie wrote: Why are those things huge? It’s not self-evident to me. I can only speak for myself, but throwing around terms like “shocking disappointment” is never going to convince me of anything. You can make similar statements about many other things.

pavel(dot)stehule(at)gmail(dot)com wrote: There are a lot of successful migrations from Oracle to Postgres that shows so that the absence of mentioned features isn’t too huge. Postgres is just not compatible with Oracle. The compatibility with Oracle is not possible without monstrous increasing size and complexity, and this is a benefit just for a small part of users. A lot of packages and concepts in Oracle are obsolete, or maybe not too well designed (from today’s perspective). After my experience I think there are a lot of things that are possible in stored procedures, but I am sure it is not good to do it, and I don’t think we need to promote these patterns in Postgres.

gogala(dot)mladen(at)gmail(dot)com wrote: ORAFCE uses schemas as the package names. However, one very practical thing is missing: session variables. Yes, you can emulate those with ON COMMIT PRESERVE ROWS temporary tables, but that’s a rather ugly hack. On the other hand, packages can easily be emulated by using Python. Having packages would make PLPg/SQL programming much prettier. It would be much prettier to group related routines into a package than to have them laying around without anything indicating that the routines are related. On the plus side, packages would make it much easier to migrate from Oracle to Postgres.

laurenz(dot)albe(at)cybertec(dot)at wote: I am not trying to belittle this, but when you are used to system A and start working with system B you always miss some features of A, until you get to know B better and figure out how to do things there.

bryn(at)yugabyte(dot)com wrote: I firmly believe that the intrinsic value of all of this has nothing to do with Oracle Database, with migrating from it to PG, or with Ada. It’s just that Oracle’s PL/SQL has a working implementation. And many people find it easier to think when they can experiment with something concrete rather than trying to hold, and run, a pretty big abstract model entirely in their head.

pg(at)bowt(dot)ie wrote: Maybe you should explain your position by way of a motivating example, involving a real world use case. Something that makes the issues concrete. Are these items compelling because of how they allow an organization to deploy a program in a production environment, complete with version control? Does it have something to do with decoupling the mutable business data stored in tables from the programs contained/run in the same database?