Postgres Pro
Downloads
Home   >   mailing lists

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL - Mailing list pgsql-general

From Pavel Stehule
Subject Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL
Date
Msg-id CAFj8pRDUysH+a4uuh2c5OHOWhE+RejDvK=dvV6yBdmNOq=Hsng@mail.gmail.com
Whole thread Raw
In response to Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL  (Bryn Llewellyn <bryn@yugabyte.com>)
Responses Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL  (Mladen Gogala <gogala.mladen@gmail.com>)
List pgsql-general
Tree view


čt 16. 12. 2021 v 20:22 odesílatel Bryn Llewellyn <bryn@yugabyte.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).



pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
Next
From: Adrian Klaver
Date:
Subject: Re: How to ensure column names are double quoted while using execute format when building a stored procedure?