"Module not found" when importing a Python package within a plpython3u procedure

Question

I am using a plpython3 stored function, on a postgres database on MacOS (installed with standard Enterprise DB package).

I can import standard python packages such as:

CREATE OR REPLACE FUNCTION foo(x double precision)
RETURNS double precision
LANGUAGE plpython3u
AS $$
import math
...
$$

I cannot, however import packages I have installed on the regular python3 directory on my machine, which is defined by brew:

$ which python3
/usr/local/bin/python3

So import foo would not work even though it would work in the regular python3 environment.

Would it be possible that the PostgreSQL server is not using the same python3 environment as me when running plpython3u? (perhaps it is using the python3 interpreter which is standard issue on MacOS etc.) How can I check on that and how could I correct the configuration, in the event?

And indeed, I created a stored function get_py that does the following:

import os
return os.popen('which python').read()

And it returned:

> select get_py()
+-----------------+
| get_py          |
|-----------------|
| /usr/bin/python |
+-----------------+

(and nothing for which python3). Which seems to demonstrate that it is not using the interpreter I want!

How do I change this?

Config info

• PostgreSQL 11.5 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0
• I am not using any virtualenv here.

Central

Answer 1

In addition to other environment variables like PGDATA in a shell script (pg_service.sh), set the PYTHONPATH='/path/to/python:/path/to/your/module'

E.g. cat /Users/postgres/pg_service.sh

export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PATH=/Library/PostgreSQL/11/bin:$PATH
export PGLOCALEDIR=/Library/PostgreSQL/11/share/locale
export PYTHONUSERBASE=/Users/postgres/packaging_tutorial
export PYTHONPATH=/Library/edb/languagepack-11/Python-3.6:$PYTHONUSERBASE
pg_ctl -D /Library/PostgreSQL/11/data -l /Users/postgres/logfile $1

Start the server:

> sudo -u postgres /Users/postgres/pg_service.sh start
waiting for server to start.... done
server started 

/Users/postgres/packaging_tutorial/example_pkg/__init__.py:

def retpy3():
    return 7/5

pg function:

CREATE OR REPLACE FUNCTION expy3()
RETURNS text
LANGUAGE plpython3u
AS $$
import example_pkg
return example_pkg.retpy3()
$$;

Output

psql  -c 'select * from expy3()';
 expy3
--------
 1.4

Atelier

Thanks: I am on Mac and I do see PYTHONPATH=/Library/edb/languagepack-11/Python-3.6. What is it? Postgres implementation of Python? (plpython3u?) – Central

From the docs Language pack installers contain supported languages that may be used with EDB Postgres Advanced Server and EnterpriseDB PostgreSQL database installers. The language pack installer allows you to install Perl, TCL/TK, and Python without installing supporting software from third party vendors. Python version 3.6.8 – Atelier

I did the above procedure (I had to start with: sudo -u postgres pg_ctl -D /Library/PostgreSQL/11/data start). Unfortunately, when attempting trying to run the CREATE OR REPLACE FUNCTION, I get: could not access file "$libdir/plpython3": No such file or directory (in other words, plpython3 seems now broken?) What did I do incorrectly? Note: echo $PYTHONPATH gives back: /Library/edb/languagepack-11/Python-3.6:/my/path/hello.py – Central

Yes, I was able to replicate that. That error means the PYTHONPATH variable was not available for the pg_ctl command. I've edited the answer to include the script and sudo command to start the server. – Atelier

Is the ability to dictate the interpreter used via PYTHONPATH something only available in the Enterprise DB port of postgre, or should that work in say, the CentOS 7 RPM for postgre? – Wingspan

@Wingspan it will work in CentOS 7 rpm for postgres too postgresql.org/docs/current/plpython-envar.html – Atelier