Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

čt 16. 12. 2021 v 20:22 odesílatel Bryn Llewellyn <bryn(at)yugabyte(dot)com>
napsal:

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

I don't think Postgres needs packages - this is a redundant concept in
Postgres, when Postgres has schemas (different from Oracle's schemas) and
extensions.

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.

This doesn't mean that we can stop developing functionality around stored
procedures in Postgres. I am working on session variables (an alternative
to package variables), and a few years ago I proposed a concept of schema's
private objects. Oracle has packages as inheritance of Ada language. The
stored procedures in Oracle use a slightly different conceptual model. It
is more like an application executed on the server side. On the other hand,
the schema in Postgres has different functionality than in Oracle, and
allows a more precious game with access rights, and although the syntax of
stored procedures is very near to Oracle, the concept is different, because
PL/pgSQL is executed inprocess inside SQL engine (all PL). More - PLpgSQL
is an easy fully interpreted language without optimization (the environment
is very well integrated with SQL engine, but still with low complexity),
and trying to repeat some patterns from PL/SQL can be very
counterproductive.

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