How to install 3rd party module for postgres pl/python?

Question

I need to import a 3rd party module inside my pl/python function. It seems pl/python uses an internal python that does not have any 3rd party modules.

I get this kind of error:

ERROR:  PL/Python: PL/Python function "to_tsvector_luc" failed
DETAIL:  <type 'exceptions.ImportError'>: No module named lucene

********** Error **********

ERROR: PL/Python: PL/Python function "to_tsvector_luc" failed
SQL state: XX000
Detail: <type 'exceptions.ImportError'>: No module named lucene

How do I install the module into pl/python, so that I can import it from inside my stored procedure code?

As you found out plpython3u uses the system Python and system site-packages not the local(user) packages. It makes sense as it running in a system service, the Postgres server. — Adrian Klaver
– Adrian Klaver, Commented Nov 2, 2021 at 15:43

Adrian · Accepted Answer · 2011-11-07 11:50:39Z

28

pl/python has access to the all the modules that the normal Python interpreter would have as long as they are in the $PYTHONPATH on the server (and the user that runs the postgres service). Does import lucene work if you run it in the Python interpreter on the server?

If your module is installed somewhere else (e.g. not dist-packages etc.), then you would need to edit your /etc/postgresql/9.1/main/environment (adjust to your PostgreSQL version) file on the server and add something like PYTHONPATH='<path to your module>'.

answered Nov 7, 2011 at 11:50

Adrian

1,8571 gold badge20 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bcattle Over a year ago

/etc/postgresql/9.1/main/environment only appears to be a thing on Ubuntu, see here

Peter Krauss Over a year ago

And about plpython3u? (today using with pg13)... 1. Checking the path of module by print(a_module.__file__), is it? 2. the [$PYTHONPATH ](postgresql.org/docs/current/plpython-envar.html) seems same for Python3, is it? 3. But about many modules? And a module installed by pip but at /home/myUser ... Please expand answer about modern (2021) details

user1225054 · Accepted Answer · 2013-10-30 23:28:07Z

20

Since modifying PYTHONPATH of postgres user will likely need a server restart, it's somewhat easier to add the path from within Python, via

from sys import path
path.append( '/path/to/your/module' )

answered Oct 30, 2013 at 23:28

user1225054

1 Comment

Peter Krauss Over a year ago

Not working... Need to add import moduleX clause? about path, is the folder where you see __init__.py , by print(a_module.__file__)? I installed chevron by pip and obtained a user-area path, /home/myUser/.local/lib/python3.8/site-packages/chevron/ , it is valid? No PostgreSQL server access-to-path problem?

antonio · Accepted Answer · 2020-05-19 16:15:11Z

6

+50

For me, it was about knowing which version Python Postgres was looking at and then installing to the /local/lib directory and NOT .local directory which is not recognised in Postgres.

In Postgres, created this function to identify the Python version it was using: .

CREATE OR REPLACE FUNCTION python_version()
    RETURNS pg_catalog.text AS $BODY$

    import sys
    plpy.info(sys.version)    
    return 'finish'
    $BODY$
LANGUAGE plpython3u VOLATILE SECURITY DEFINER

Execute function using the following:

select python_version()

In Python:

import sys
sys.version

Both the script and Python said: INFO: 3.7.3 (default, Aug 20 2019, 17:04:43)

pip3 install placed the library into this directory: /home/username/.local/lib/python3.7/site-packages

To make the package available to ALL users (including Postgres). I used umask:

sudo su
cd ~
umask 022
pip3 install <package name>

NOTE: The package installs in: /usr/local/lib# cd python3.7/dist-packages NOT in /usr/lib/python3/dist-packages#

edited May 19, 2020 at 16:15

antonio

18.3k4 gold badges48 silver badges62 bronze badges

answered Oct 8, 2019 at 9:33

Dr Ian Gregory

1873 silver badges6 bronze badges

2 Comments

Peter Krauss Over a year ago

Yes, need ugly pip3 procedure with sudor and unmask 022.

Peter Krauss Dec 6, 2024 at 11:39

(find it again after years) This is the best answer! Whithout umask the Python import some_exotic_lib doesn't work in PostgreSQL.

Muhammad Numan · Accepted Answer · 2021-11-08 10:22:53Z

0

I think the 43 pl/python has access to the all the modules that the normal Python interpreter would have as long as they are in the Pythonpath on the server and user that is using PostgreSQL.

If your module is installed somewhere else (e.g. packages), then you would need to edit your /src/postgresql/9.1/main/environment (adjust to your PostgreSQL version) file on the server and add something like Pythonpath=>*'path to your module'**.

answered Nov 8, 2021 at 10:22

Muhammad Numan

2474 silver badges20 bronze badges

Comments

winwin · Accepted Answer · 2023-05-12 16:04:01Z

0

For Ubuntu 22.04:

Install your module: sudo pip install foo.
Find out where your python stores installed modules. For me it was /usr/local/lib/python3.10/dist-packages/ - where obviously python3.10 is your installed python version. The command pip3 show <your package name here> should help you out, if your path is different. E.g.: pip3 show numpy.
Now, you need to change the permissions to your module files, because postgres is running as postgres user, but the files of your module will belong to root. You should do: sudo chmod -R 755 <path to your module>. E.g.: sudo chmod -R 755 /usr/local/lib/python3.10/dist-packages/test_module.

And now your module should be working.

answered May 12, 2023 at 16:04

winwin

2,1241 gold badge21 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to install 3rd party module for postgres pl/python?

5 Answers 5

2 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related