When (or why even) use PLPython(3)u

Question

As I gain more experience with PostgreSQL I start to question the existence of PLPython. It's considered an "untrusted" language https://www.postgresql.org/docs/10/plpython.html

What I am wondering is, when or why would anyone need to use this? PLPGSQL is already quite a strong language that allows you to do a lot of things. Has anyone here had the need to use it, and if so, for what?

Answer 1

I find pl/pgsql tedious for general programming, and slow to program in and slow to execute. And missing a lot of functionality--some of which is missing because it has to be in order to be a trusted language (no IPC or network), and others just because it hasn't been built and it lacks a mechanism for libraries/modules/packages.

I've used plpython3u get access to chemical intelligence libraries from inside the database. re-implementing the functionality of those libraries from scratch would be theoretically doable, but utterly impractical. Doing it in pl/pgsql would be utterly impractical squared.

I've done the same thing with plperlu and plperl, to get access to a variety of CPAN Perl modules. If those modules happened to have been in python rather than Perl, I would have used plpythonu rather than plperl(u).

I've also used plperl (not plpythonu but just because I prefer Perl over python) to implement functions which I wanted to be available both inside the database through SQL, and outside in our procedural code. I could have implemented them in pl/pgsql, but that would be annoying to do, and also means they would need two implementations in two languages. Alternatively I could have just implemented them in the database, and connect to the database from standalone Perl when I want to call the functions from there. But that would sometimes mean establishing a database connection that would otherwise be unneeded in that program, and would be an abuse of one of our more limited resources, as databases are much harder to scale than standalone Perl scripts. It would also introduce latency.

Answer 2

It's untrusted because in fact you are able to use OS system calls from plpython function - read/write files (including pgdata catalog) and system processes, under postgres system user. So you should be really careful with it. But let me show you a few cases when plpython can be useful.

1. When you need to communicate with the outside world (which is not a good idea in general but it's still possible). You can easily write a stored procedure which will send SMS using some external HTTP REST API or generate and upload file on disk.
2. When you need to process a lot of non-SQL data, such as JSON. Processing JSON is much easier and faster in Python because JSON types map to Python internal types easily.
3. When you need to perform math calculations. When you need to implement some difficult algorithm which involves a lot of calculations using plpgsql is a bad idea. This will be definitely faster in plpython than in plpgsql because plpgsql actually invokes a separate SELECT statement to calculate every single expression while plpython doesn't.