PostgreSQL PL/Python: call stored procedure in virtualenv

Question

When I call a PostgreSQL PL/Python stored procedure in my Python application, it seems to be executed in a separate process running as user postgres. So far, this only had the side effect that I had to make my logfile writable for both myself and the database user, so application and stored procedure can both write to it.

Now however, I started using virtualenv and added a number of .pth files to my ~/.virtualenvs/virt_env/lib/python2.7/site-packages/ folder that add the paths to my modules to the Python path.

When the stored procedure is executed, user postgres is not in the same virtual environment as I am, so the stored procedure does not find my modules. I can modify PYTHONPATH in the global PostgreSQL environment, but I have to change that every time I switch virtual environments - which is kinda against the purpose of virtualenv...

How can I extend the Python path for stored procedures ?

UPDATE:

A similar question has been asked and the resolution there was to modify the PYTHONPATH environment variable in Postgres; however, it seems that there is no standard way to specify environment variables for PostgreSQL; at least, it is not a viable solution on Mac OSX.

Kittenish

Answer 1

There is a way to do it, as it turns out. Since version 1.6 or there abouts, virtualenv comes with a script activate_this.py, which can be used to set up an existing interpreter to access that particular virtualenv.

exec(open('/Some/VirtualEnv/Directory/myvirtualenv/bin/activate_this.py').read(), 
dict(__file__='/Some/VirtualEnv/Directory/myvirtualenv/bin/activate_this.py'))

And as a fully-realized plpython function:

CREATE OR REPLACE FUNCTION workon(venv text)
  RETURNS void AS
$BODY$
    import os
    import sys

    if sys.platform in ('win32', 'win64', 'cygwin'):
        activate_this = os.path.join(venv, 'Scripts', 'activate_this.py')
    else:
        if not os.environ.has_key('PATH'):
            import subprocess
            p=subprocess.Popen('echo -n $PATH', stdout=subprocess.PIPE, shell=True)
            (mypath,err) = p.communicate()
            os.environ['PATH'] = mypath

        activate_this = os.path.join(venv, 'bin', 'activate_this.py')

    exec(open(activate_this).read(), dict(__file__=activate_this))
$BODY$
LANGUAGE plpythonu VOLATILE

(The extra PATH mungery is needed since by default PATH isn't available in plpython os.environ - activate_this.py has a fix checked in that should roll w/ the next point release (which should be 1.11.7 or 1.12)

( taken mostly from https://gist.github.com/dmckeone/69334e2d8b27f586414a )

Comstock

I am not sure that this virtualenv paths remains active across multiple plpython calls, because the interpreter is new on every call. I suppose you are assuming we call workon() from all plpython methods at the very start? – Cleanse

Actually, within a session (db connection) all plpython functions share an interpreter, though they get a fresh execution state (barring the global GD data-sharing dictionary) - you do not start a new interpreter for each query. Don't call it multiple times, the sys.path will continue to grow – Comstock

Answer 2

Normally I'd say it's not really a good idea but you may be able to follow this question.

What you could do is run multiple instances of PostgreSQL with different environments to allow for various PYTHONPATH settings.

Augustusaugy