"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.

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