Database Administrators Asked by Chessbrain on October 27, 2021
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?
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.
Answered by jjanes on October 27, 2021
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.
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.Answered by pensnarik on October 27, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP