I would like plpython3u to use a virtual environment for python. I have seen some (old and not particularly clear) instructions for virtualenv at PostgreSQL PL/Python: call stored procedure in virtualenv. However I havent seen anything for python venv. Can this be done and if so, how can it be done? Thanks.
CCl.
If you only need one venv environment for PL/Python, you can simply modify ExecStart in the PostggreSQL unit file to load venv activate script.
Below is excerpted from the PostgreSQL 14 systemd unit file of PGDG YUM installation (My venv ls located at /var/lib/pgsql/plpython/).
ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA}
#ExecStart=/usr/pgsql-14/bin/postmaster -D ${PGDATA}
ExecStart=/bin/bash -c "source /var/lib/pgsql/plpython/bin/activate && /usr/pgsql-14/bin/postmaster -D ${PGDATA}"
Note that you need to copy the unit file postgresql-14.service from /usr/lib/systemd/system/ to /etc/systemd/system/ and execute systemctl daemon-reload to take effect.
Then you can check venv with the pkg_resources module inside PL/Python anonymous block (I install pyyaml in my venv environment).
postgres=# CREATE EXTENSION plpython3u ;
CREATE EXTENSION
postgres=# DO $$
import pkg_resources
installed_packages = pkg_resources.working_set
installed_packages_list = sorted(["%s==%s" % (i.key, i.version)
for i in installed_packages])
plpy.notice(installed_packages_list)
$$ LANGUAGE plpython3u;
NOTICE: ['pip==9.0.3', 'pyyaml==5.4.1', 'setuptools==39.2.0']
DO
postgres=#