PL/* ecosystem: potential, growth, problems

[yrashk]

Arguably, one of the exciting capabilities of Postgres is the ability to integrate various programming languages with it. And we have some, both in core and outside – but it feels like most of them enjoy limited use ¹. It would be great to further grow Postgres' capabilities in improving existing PLs and making it easier to develop new, reliable PLs.

On my end, I have a proof of concept for PL/Prolog, have done some work augmenting plpython's usability and have the desire to get a lot more languages to the mix (to name a few: Ruby, Racket, OCaml ²). Coming out of this angle, I felt that there are at least a couple of things that would be interesting to work on (by no means an exhaustive list):

1. Guidelines and reference material for building PLs so it's a bit more straightforward for language champions to follow through their efforts.
2. Conformance specification: define the surface should a PL cover and the details
3. Conformance tests suite: ensuring there's a common baseline.
4. Explore support for versioned language runtimes ³
5. A unified approach (strategy) to shipping dependencies? ⁴
6. Explore opportunities of shipping the code in "native units" with host language's idiomatic annotations that would define things like function signatures ⁵

What do you think about this? Do you have any ideas, insights and knowledge that you can contribute to this cause? What else would be interesting to collaborate on within this space?

I invite those interested in programming languages support in Postgres to participate in this discussion. Also, if you know any PL/* project maintainers, please invite them to this conversation!

---

¹ It's more on the anecdotal/perception side of things – would actually be interesting to give a shot to quantify this statement)
² The more the merrier, especially if we can figure out good ways to integrate languages with significant runtimes
³ For example, with Python 3, there are significant differences between minor versions. Would supporting multiple runtime versions (when technically feasible) be useful?
⁴ I've done experimental work on provisioning Python requirements using supplied requirements.txt for omni_python – but I'd like something more generalized.
⁵ Intuitively, most people want to develop their host language code in their ".py" files, not in SQL files – this is better for tooling, comprehension, etc.

[tlewis-ba]

Hello! Our team built pl/dotnet, and we have lots of input to offer in this space.

We also built a cross-PL test suite, which we then completely re-wrote and will release publicly later this year. The goal there was to facilitate more cross-PL cooperation, so I definitely love this idea.

[yrashk]

@tlewis-ba that sounds great! I wonder what's preventing the public release at this point – does it need some further work, or are there other concerns?

[tlewis-ba]

We're putting the finishing touches on it. Not really sure of the timeline, but it's pretty far along.

[postsql]

Another thing each PL/ should add is an easy way to add its version of standard pgbench tests, so it would be really easy to run equivalent tests to the "TPC-B-Like" test.

This should consist of a single SQL file which creates the functions corresponding to each test and *.pgbench files to be run using pgbench ... -f

I started adding this directly to pgbench for pl/pgsql in https://commitfest.postgresql.org/43/4105/

[postsql]

One long-overdue addition to pl/* language support is supporting streaming functions in a PL, so for example one could have a pl/python function pyenumerator() which has

i = 1
while True:
    yield i
    i += 1

and then use it as

SELECT * FROM pyenumerator() LIMIT 5

to get numbers from 1 to 5

This can easily be done for C functions but with current implementation of PL functions which always Materialize the set result the above will result in running out of memory while materializing

[tlewis-ba]

Set-returning functions do this, and so does pl/python; look for "SRF" in plpy_exec.c.

[postsql]

SRFs themselves do, but the infrastructure around them tries to materialize the result
try:

hannuk=# create or replace function plpycounter() returns setof int language plpython3u AS $$
i = 1
while True:
    yield i
    i += 1
$$;
CREATE FUNCTION
hannuk=# select * from plpycounter() limit 5;
...

and watch your session run out of memory

[tlewis-ba]

Are you sure that isn't a problem in your sql?

tlewis=# create or replace function plpycounter() returns setof int language plpython3u AS $$
i = 1
while True:
    yield i
    i += 1
$$;
CREATE FUNCTION
tlewis=# select plpycounter() limit 10;
 plpycounter 
-------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10
(10 rows)

Works fine for f# too:

tlewis=# CREATE OR REPLACE FUNCTION fsharp_counter() RETURNS SETOF INT AS $$
let rec fsharp_counter (n: int) : seq<Nullable<int>> =
    seq {
        yield Nullable(n)
        yield! fsharp_counter (n + 1)
    }

fsharp_counter 1

$$ LANGUAGE plfsharp STRICT;
CREATE FUNCTION
tlewis=# select fsharp_counter() limit 10;
 fsharp_counter 
----------------
              1
              2
              3
              4
              5
              6
              7
              8
              9
             10
(10 rows)

tlewis=#

[postsql]

I should have recorded the version of PostgeSQL I tested on.
My latest tests for v 14 up indeed work correctly

Thanks for pointing this out for me!

[jcflack]

... making it easier to develop new, reliable PLs.
...
1. Guidelines and reference material for building PLs so it's a bit more straightforward for language champions to follow through their efforts.

There is current related work in PL/Java, aimed at the existing and (so far) untapped ecosystem of other languages that run on the JVM. Currently unmerged work in progress (you have to build from the branch) asks the question "what should a higher-level version of PostgreSQL's language-handler API look like, so you could write a language handler in Java to implement PL/some JVM language here?"

The first example is the language handler for the Glot64 language (I do not expect Glot64 to become a popular language 😃), an implementation of this language handler interface.

The handler interface seeks to divvy up the PostgreSQL function-call convention in a way that encourages a staged-programming approach: prepare is expected to return a Template that depends only on the routine's catalog definition in pg_proc (possibly with unresolved polymorphic types) that will be cached for as long as the pg_proc row lives; the Template has a specialize method that should take into account the argument types and context at a particular call site (FmgrInfo object) and return a Routine that will be cached there using fn_extra, and the Routine has a call method that handles each actual call.

Naturally, it's up to the handler how sophisticated it wants to be: prepare on a routine with no polymorphic parameters could return a Template that always returns the same Routine, or prepare and specialize could do minimal work and always return a Routine that resolves everything at call time, or specialize for an IMMUTABLE or STABLE routine could detect that the argument expressions are stable, and return a Routine that memoizes and returns its first result (though that may be something PostgreSQL's own optimizer already does).

The specific topic of JVM languages and handlers in Java might not be anybody else's chief interest, but I'm definitely interested in comments on this overall way of approaching a high-level language handler API.

The branch will build and allow simple tests of Glot64 (or of your own first crack at a JVM language handler!), but a whole lot of things on the PR's to-do list remain unchecked, including concurrency details and access-control contexts; there won't be any difference between trusted/untrusted, and anything nontrivial is going to crash. But the basic outlines are there.

6. Explore opportunities of shipping the code in "native units" with host language's idiomatic annotations that would define things like function signatures 5
   ...
   5 Intuitively, most people want to develop their host language code in their ".py" files, not in SQL files – this is better for tooling, comprehension, etc.

PL/Java, by the way, does use Java annotations this way (in released versions, even!), which is especially handy given the extra step in PL/Java's usage model: instead of supplying source code directly in CREATE FUNCTION ... AS, you separately compile the sources, load the jar file into the database, and the CREATE FUNCTION SQL commands refer to that.

Based on the annotations, the Java compiler writes an extra file with the needed SQL commands, to be included in the jar, where PL/Java executes the commands automatically when the jar is loaded.

A design decision in the annotation processor is that it doesn't just blindly write out SQL following the annotations, but it knows the same rules PostgreSQL will enforce about which option combinations, for example, are valid. Therefore, you get useful error reporting as soon as you compile the sources, rather than waiting for errors from PostgreSQL when loading the jar.

Annotations are provided for defining functions, triggers, UDTs, operators, casts, and aggregates. A nice demonstration is this pull request that compares some substantial example code written by Bear Giles, and demonstrating functions, UDTs, operators, casts, and aggregates, with the same code using the annotations instead of SQL written by hand.

In PLs that don't have PL/Java's separate compilation step, any annotations might be used more directly, not by generating a file of SQL commands as PL/Java does. And there might be less value in having them do their own validity checks, as PostgreSQL's own error reports would be immediate. But PL/Java's annotations might serve as one proof of concept.