1

When a PL/Python procedure is executed, the python code is executed by a Python interpreter. My question is, is the Python interpreter running as a separate process, or is it a shared library that gets linked to the calling databases process?

I'm concerned about what happens when we call something like plpy.execute(...). If the python interpreter is running as a separate process I imagine there would be a lot of overhead involved in passing the result of the sql query back to the python interpreter, which would require reading from a file or pipe.

Adam Van Oijen
  • 435
  • 3
  • 8
  • 17

1 Answers1

1

The language handler function (plpython3_call_handler()) loads the plpython3.so library into the PostgreSQL process, which is linked to libpython3.so. So the interpreter is loaded into the backend, it is not executed as a separate process (multiprocessing/multithreading is not allowed in PostgreSQL client backends, with the exception of parallel workers).

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks that answers my question. Are there other sources of overhead when using PL/Python other than those specific to executing python code in the first place? – Adam Van Oijen Sep 08 '22 at 04:12
  • There is always overhead. Every function call is overhead, and there is definitely an overhead in loading and initializing the Python interpreter the first time you call a PL/Python function in a database session. – Laurenz Albe Sep 08 '22 at 05:32
  • I played around with PL/Python a bit and I can see executing queries from within pl/python functions is particularly slow. Makes me wonder how data is passed between the backend and python interpreter. I posted a separate question if you are willing to look: https://stackoverflow.com/questions/73657548/calling-plpy-execute-in-pl-python-much-slower-than-calling-execute-in-pl-pgsql – Adam Van Oijen Sep 09 '22 at 05:11