A short page on PL/Python

PostgreSQL allows for functions and procedures to be written in languages other than SQL. The team was interested in PL/Python because the rest of the project is currently written in Python. The teams also wants to incorporate some form of testing.

When trying to find out whether using other languages in functions, I’ve learned a few things. First, python is considered “unsafe” because the interpreter has basically root access to the server. Concerns have been dispelled by the team because technically there is only one user that accesses the database from within openIMIS so that there is no change once PL/Python is used.

When it comes to speed, I have found a blog (link) that has done some benchmarks for different procedural languages that PostgreSQL supports. The blog post is six years old now, so improvements may have been made since then. While synthetic benchmarks seem to show that raw SQL is still the fastest method of interacting with the SQL database, the author and the comments section both speculate that in the real world IO operations would be a bottleneck that negates all performance benefits that SQL may have. All this means that nothing stands in the way of openIMIS using Python in postgres in the future.

Using PL/Python is as simple as specifying the language used in the function. If you go back and take a look at the functions and procedures scripts I have provided on the Database Structure Migration Process page you may notice that at the end of every function (or procedure) I have used the command “LANGUAGE plpgsql;“. Specifying “LANGUAGE plpythonu;“ is enough to start using Python instead (the “u” at the end signifies “unsafe” as the people of postgres have concerns because of the whole root access thing).

Here’s the blog post I mentioned: http://okbob.blogspot.com/2014/05/a-speed-of-pl-languages-for-atypical.html