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

Folks who develop applications for Oracle Database have had the features that the subject line of this email lists
sincethe arrival of PL/SQL in the early nineties. The advantages are self-evident to these programmers; and their lack
comesas a shocking disappointment when they start to write application code for PostgreSQL*. The absence of packages
andinner 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
foremostto 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
featuresthat I mentioned. If there is any such thinking, please let me know. I’m not a C coder but I’d be very
interestedin 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
startedwith Yugabyte, Inc. At least some people on this list have heard of YugabyteDB and know that it uses Postgres’s
SQLprocessing code “as is” (currently Version 11.2, but presently Version 13) on top of its own implementation of a
distributedstorage layer (inspired by Google Spanner). 

On 12/16/21 11:22, Bryn Llewellyn wrote:
> Folks who develop applications for Oracle Database have had the features that the subject line of this email lists
sincethe arrival of PL/SQL in the early nineties. The advantages are self-evident to these programmers; and their lack
comesas a shocking disappointment when they start to write application code for PostgreSQL*. The absence of packages
andinner 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
foremostto 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
featuresthat I mentioned. If there is any such thinking, please let me know. I’m not a C coder but I’d be very
interestedin reader any ordinary prose that describes how these features might be exposed to the PostgreSQL application
developer.

Not following. To be exposed they have to exist and that is not the case 
in the community Postgres. The relevant question would seem to be, how 
do I get these features built?

> ________________________________________________________________________________
> 
> * Full disclosure: I was the product manager for PL/SQL, working at Oracle HQ, from about 2000 through 2019 when I
startedwith Yugabyte, Inc. At least some people on this list have heard of YugabyteDB and know that it uses Postgres’s
SQLprocessing code “as is” (currently Version 11.2, but presently Version 13) on top of its own implementation of a
distributedstorage layer (inspired by Google Spanner).
 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

adrian.klaver@aklaver.com wrote:

bryn@yugabyte.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 reader any ordinary prose that describes how these features might be exposed to the PostgreSQL application developer.

Not following. To be exposed they have to exist and that is not the case in the community Postgres. The relevant question would seem to be, how do I get these features built?

Bryn continued:

* 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 12/16/21 12:36, Bryn Llewellyn wrote:
> //
>> /adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:
>> /
>>

>>
>> Not following. To be exposed they have to exist and that is not the 
>> case in the community Postgres. The relevant question would seem to 
>> be, how do I get these features built?
>>
>>> /Bryn continued:/
>>>
>>> * Full disclosure: I was the product manager for PL/SQL, working at 

> 
> Oops. I did a typo. I’d meant to write “I’d be very interested in 
> *reading* any ordinary prose…”

That I figured out:)

> 
> I can’t parse your “To be exposed they have to exist and that is not the 
> case…” Do you mean that the rumor that I heard is wrong and that nobody 
> has said to the Postgres community that they’ve embarked on, or at least 
> are interested in, implementing what I’m asking about?

Not that I know of.  What was being danced around is that Oracle 
compatibility is a key feature of EDB's business model:

https://www.enterprisedb.com/products/edb-postgres-advanced-server-secure-ha-oracle-compatible

> 
> I had assumed that the answer to “How do I get these features built?” 
> was “Write a C implementation and submit it for consideration”. But I 
> can’t do that. The obvious Google searches like “Submit enhancement 
> request for PostgreSQL” turn up only informal emails to lists like this. 
> Is there a better answer?
> 
Not really, though if you want to bring this up --hackers is the best list.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Dec 16, 2021 at 11:22 AM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> The advantages are self-evident to these programmers; and their lack comes as a shocking disappointment when they
startto write application code for PostgreSQL*. The absence of packages and inner subprograms is huge.
 

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.

Any functionality that gets added to PostgreSQL should ideally be
compelling to users that have not worked with Oracle in the past.
Maybe that happens to result in a feature that very much looks like
what you already have in mind, or maybe there are significant
differences. If you're not going to meet others closer to where they
are, then how can you expect it for yourself?

-- 
Peter Geoghegan

On 12/16/21 16:48, Pavel Stehule wrote:
> 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.
>
Yes, 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. And you do want that, don't you?

I am very well aware of the animosity toward the Oracle community and 
the reasons for that animosity. Oracle wiped the floor with the 
predecessor of Postgres, the database called "Ingres". Sandra Kurtzig, 
the Ingres CEO at the the time, and Michael Stonebraker were both 
involved in very public spat with Larry Ellison. Stonebraker is still 
very much a factor in Postgres community and I doubt that his feelings 
toward the Larry and his company have got any warmer with years. 
Postgres was created with the idea of fighting the Dark Lord in the land 
of Belmont, CA. However, if it is the goal of the community to have 
users convert from Oracle to Postgres, than making that conversion easy 
would be conducive to that goal.  A long time ago a former pastry baker 
wrote the following article:

https://www.toolbox.com/tech/data-management/blogs/why-postgresql-doesnt-have-query-hints-020411/

Disclosure: I am the Oracle DBA that Josh Berkus is talking about in 
that article. Recent events with "advice" to a very well known Oracle 
personality testify to the fact that the emotions haven't changed much 
since that article was written. Of course, Oracle is still the most 
popular database in the world.


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On Thu, Dec 16, 2021 at 6:39 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:
> I am very well aware of the animosity toward the Oracle community and
> the reasons for that animosity. Oracle wiped the floor with the
> predecessor of Postgres, the database called "Ingres". Sandra Kurtzig,
> the Ingres CEO at the the time, and Michael Stonebraker were both
> involved in very public spat with Larry Ellison.

These events happened several years before I was born.

> Stonebraker is still
> very much a factor in Postgres community and I doubt that his feelings
> toward the Larry and his company have got any warmer with years.

No, he isn't. I think that Stonebraker appeared at a few events that
were hosted by EDB, but to the best of my knowledge that's the extent
of his involvement in the project after university POSTGRES. I've
personally never met the man, or communicated with him online, and
I've worked on Postgres more or less full time for a full decade now.
As far as I'm aware he hasn't ever publicly posting to any of the
mailing lists.

-- 
Peter Geoghegan

On Thu, 2021-12-16 at 11:22 -0800, Bryn Llewellyn wrote:
> The advantages are self-evident to these programmers

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.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

bryn@yugabyte.com wrote:

The advantages are self-evident to these programmers…

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.

«

Modular design recommends exposing functionality through a purpose oriented interface and hiding all implementation details from the API’s user. A package achieves this with declarative syntax via the spec/body separation. The body encapsulates as many (top-level) subprograms as you want. Each of these is visible to all of its peers. But none is visible outside of the package unless the spec declares that it should be. This is a simple opt-in scheme.

Inner subprograms are a very nice bonus because they allow locally useful helpers to be implemented right where they’re needed in one subprogram so that they’re invisible to all other subprograms in the body. The scope rules here a simply an extension of what PG already supports with block statements inside block statements.

There’s also the business of globals, at any level, and package-level globals that bring package state. Some programmers have religious objections here. But the value of constants declared in the package spec seems to be seen by all who program using packages as only useful.

»

On Fri, Dec 17, 2021 at 11:43 AM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> Modular design recommends exposing functionality through a purpose oriented interface and hiding all implementation
detailsfrom the API’s user. A package achieves this with declarative syntax via the spec/body separation. The body
encapsulatesas many (top-level) subprograms as you want. Each of these is visible to all of its peers. But none is
visibleoutside of the package unless the spec declares that it should be. This is a simple opt-in scheme. 

I still don't get it. It sounds like you're mostly talking about
encapsulation, or Information hiding, for stored procedures. I can
certainly see how plpgsql doesn't do those things very well, but it
still seems like there might be a lot of nuance that isn't getting
across. The list of specific features that seem to be missing are not
unreasonable, individually, and yet it feels like I cannot see some
bigger picture that's apparent to you.

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?

--
Peter Geoghegan

pg@bowt.ie wrote:

bryn@yugabyte.com wrote:

Modular design recommends exposing functionality through a purpose oriented interface and hiding all implementation details from the API’s user. A package achieves this with declarative syntax via the spec/body separation. The body encapsulates as many (top-level) subprograms as you want. Each of these is visible to all of its peers. But none is visible outside of the package unless the spec declares that it should be. This is a simple opt-in scheme.

I still don’t get it. It sounds like you’re mostly talking about encapsulation, or Information hiding, for stored procedures. I can certainly see how plpgsql doesn’t do those things very well, but it still seems like there might be a lot of nuance that isn’t getting across. The list of specific features that seem to be missing are not unreasonable, individually, and yet it feels like I cannot see some bigger picture that's apparent to you.

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?

pavel.stehule@gmail.com wrote:

bryn@yugabyte.com wrote:

Yes, I did read your “schema variables” post on your site “Pavel Stehule’s blog — Some notes about PostgreSQL”. It gives me a very good idea about what you have in mind.

But as I’ve come to understand the term “Functional Spec”, this denotes a formal deliverable that a product development (sub)group owns and maintains collectively. Especially, it has a status which, eventually, is set to “Approved”. And apart from minor bug fixes, no code makes its way into a released version of the (sub)product in question unless the group has approved its Functional Spec.

Is this model not used for the PostgreSQL system?

Development model in Postgres is not too formal, the specification can be changed until the last moment, the main word has commetter with an agreement of the author of patch and all other people. The development of Postgres is much more agile than waterfall.

bryn continued:

Your blogpost and all the comments are dated Feb 2018—so almost four years ago now. What needs to happen for your project to be exposed in a PG Release? And which release might this be?

Technically, this patch is not too complex, but a) it was harder to find cleaner with good performance implementation in architecture (the current architecture knows queries and utility commands, and LET is something between). b) it was harder to find an agreement about specification because global temporal objects like global temporary tables or session variables are not in Postgres today (there is not long experience with this feature). Unfortunately 1. the related part of standard SQL/PSM is not widely accepted and this part of the standard is almost dead , 2. the introduced modules are +/- Postgres's schema, so it is a really redundant concept, 3. SET command (in SQL/PSM (ANSI/SQL) is used for different purpose in Postgres, and I had to use keyword LET (there is not possibility to use keyword SET without compatibility break), c) there was long discussion if variables should be transactional or non transactional (I strongly support not transactional by default - like any other databases does, and transactional behavior will be optional in next step).  

I invite any help with code and documentation review (and support in discussion) - I am not native speaker, and my English is very poor. I hope there is a change to commit this patch in Postgres 15. But it depends on commiter's capacite - and there are a lot of patches in the queue.

You can watch the progress of this work on pgsql-hackers mailing list

older https://www.postgresql.org/message-id/flat/CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ@mail.gmail.com

current https://www.postgresql.org/message-id/flat/CAFj8pRD053CY_N4=6SvPe7ke6xPbh=K50LUAOwjC3jm8Me9Obg@mail.gmail.com
 

bryn continued: Back to the substance of your proposal, 

1. I see that a schema variable will be another kind of object in the class that has tables, views, sequences, indexes, UDTs, procedures, functions, and so on. So it’s persisted within a database; it’s owned by a user; and it’s localized in a schema. Does it have its own namespace too? For example, can a table x, a function x(), and a schema variable x all live in the same schema s in some database. And can I use the normal qualified name syntax to access a schema variable?

variables have their own namespace, because they have their own catalog table. It is designed like any catalog object - so you can use (or you don't need) to use a qualified identifier. But there is not any schema scope in Postgres now. Anything depends on SEARCH_PATH setting. So variables can be in the same schema with tables and functions (the access rules are the same).

bryn continued: 2. It seems, then, that all the familiar notions, and especially the ability for a non-owner to access it apply. I see that the SELECT privilege governs who can read the value of a schema variable. But there seems to be no mechanism that governs who can change the value of a schema variable. It looks like only the owner can change the value—using the new LET SQL statement. Does this mean both that a top-level call from a client session that’s authorized as the owner can change it and a “security definer” subprogram with the same owner can change it—but that there is no explicit (say, WRITE) privilege for this. Is this what you intend? If so, why not have a WRITE privilege?

In the last patch I renamed schema variables to session variables (on the community request). You can grant READ or WRITE rights to other users by command GRANT, or you can write a security owner function, if you need more precious work with sensitive data.

bryn continued: 4. You said “security definer function”. Is this what you mean? Or do you mean ANY function or procedure as long as the current user (i.e. the owner of the most tightly enclosing security definer unit) is the schema variable’s owner?

inside the security owner function you are running under functions's owner identity. It can have the same identity as the variable's owner. It is common pattern in Postgres (not just in Postgres)

bryn continued: 5. Could you please consider allowing a CONSTANT schema variable (with the usual syntax and requirement for an initialization expression)? One very popular use of a spec-level package variable is for a universal constant that has to be accessed in several places—like, for example, the conversion factor between some metric unit and some imperial unit.

There is it - it is not named CONSTANT, but IMMUTABLE (because CONSTANT needs to introduction of new SQL keyword, and IMMUTABLE is already used keyword (new keywords can introduce some compatibility issues))

CREATE IMMUTABLE VARIABLE iv AS int DEFAULT 100;
-- should to fail
LET iv = 10000;
ERROR:  session variable "iv" is declared IMMUTABLE

bryn continued: 3. What is the precedence scheme? For example, if a SQL statement in a PL/pgSQL unit has a restriction like this:

   …where col = x…

and x is both the name of an in-scope variable (or formal parameter) in the unit and the name of a schema variable? When the table has a column called x, then there’s (at least sometimes) no way round a run-time collision error except to rename one of the X’s. (Qualifying the names doesn’t fix it.) Will it be the same with schema variables? The question extends to ordinary assignment statements that become SQL statements under the covers:

  v := a + b;

where b happens to be both an in-scope variable and a schema variable.

You can handle collisions by using qualified identifiers. For PL/pgSQL you can use block labels, for other objects schema.

From PostgreSQL’s SQL perspective the session variables are common database objects (contra PL/SQL where package variables are PL/SQL language objects), and SQL disallows ambiguity. This is a little bit more complex problem, because session variables can be used everywhere in Postgres (not just in PL/pgSQL).

PL/pgSQL doesn’t see session variables like something special - PL/pgSQL runtime doesn’t see session variables ever (the work with session variables are done one level deeper) (the usage of session variables are fully transparent for this environment) so there are the precedence rules without change, but you can use with session variables inside PL/pgSQL (but from implementation perspective it is just any other SQL object).

https://www.postgresql.org/docs/9.6/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

and you can set different precedence 

#variable_conflict error

#variable_conflict use_variable

#variable_conflict use_column

but I strongly don't advise it. From my perspective, allowing collisions was a significant Oracle's PL/SQL design error.

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