Interactively_Debugging_Plpython

title: Interactive-Debugging of PL/Python layout: post comments: true —

1. Setup Environment ( updated 2017.10.03 )

• recommend compiling postgreSQL with virtual environment interpreter:

  PYTHON_EXEC="some/python/exec/created/by/virtualenv"
    
  ./configure PYTHON="$PYTHON_EXEC --with-python ..."
    
  make
  sudo make install
  (etc...)
  

• also recommend storing reusable functions in pgSQL’s global dictionary GD (See more here)

a. Using an IDE (PyCharm, WingIDE, Eclipse, etc..):

import sys
sys.path.append('/usr/local/lib/python2.7/dist-packages/pycharm-debug.egg')
import pydevd
pydevd.settrace('10.0.1.53', port=50003, stdoutToServer=True, stderrToServer=True)

b. iPython and ipdb

Embed iPython kernel in script (e.g., immediately preceeding a function f(x)):

import IPython as I
I.embed_kernel()

Put ipdb tracer(s) after the kernel call (i.e., inside of f(x)):

import ipdb
ipdb.set_trace()

Execute PL/Pythonu/Python code:

DO LANGUAGE PLPYTHONU
$BODY$
import sys
sys.path.append('/home/ubuntu/tmp.py')
import tmp
reload(tmp)             # resets pgsql cache
tmp.do_something(plpy)  # note plpy as arg
$BODY$;

If executing the above via command-line (e.g., sqlalchemy, pandas, etc..),standard out may provide the “…connect with –existing kernel-{kernel pid}.json” iPython message. If not, check the newest iPython process ps -awx | grep ipython, or check the directory below (or a similar path).

Connect to the kernel:

sudo su postgres
cd /var/lib/postgresql/.local/share/jupyter/runtime
# last bit below assumes a clean runtime directory
ipython console --existing kernel-* && rm *

Call the f(x).