PL/Python in YugabyteDB (build with yugabyte)
Tags: yugabytedb, postgres, distributed, sql
Published: 2023-08-30 14:35:19

In this series, I explained how to use PostgreSQL extensions. Some are easy and some are more difficult, depending on their dependencies. PL/Python is powerful, as you can create procedures and functions in Python, but maybe too powerful to be enabled by default as it can give wide access to the host. Another reason for not building it by default in YugabyteDB releases is about its dependencies: you can use `plpython3u` only if `python3` is installed on the host.

Here is how to build a release of YugabyteDB, from the source, with Python included. Note that the effort to make it work is not mine but a [community contribution](https://github.com/yugabyte/yugabyte-db/issues/8463#issuecomment-1694386321).

I've put all steps in the following Dockerfile:
```yaml
FROM almalinux:8 as build
RUN dnf -y update &&\
    dnf groupinstall -y 'Development Tools'
# get YugabyteDB sources
ARG YB_TAG=2.18
RUN git clone --branch ${YB_TAG} https://github.com/yugabyte/yugabyte-db.git
WORKDIR yugabyte-db
# install dependencies and compilation tools
RUN dnf install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
RUN dnf -y install epel-release libatomic rsync python3-devel cmake3 java-1.8.0-openjdk maven npm golang gcc-toolset-12 gcc-toolset-12-libatomic-devel patchelf glibc-langpack-en ccache vim wget python3.11-devel python3.11-pip clang ncurses-devel readline-devel libsqlite3x-devel
RUN mkdir /opt/yb-build
RUN chown "$USER" /opt/yb-build
# Install Python 3
RUN alternatives --remove-all python3
RUN alternatives --remove-all python
RUN alternatives --install /usr/bin/python python /usr/bin/python3.11 3
RUN alternatives --install /usr/bin/python3 python3 /usr/bin/python3.11 3
# add #include "pg_yb_utils.h" to src/postgres/src/pl/plpython/plpy_procedure.c
RUN sed -e '/#include "postgres.h"/a#include "pg_yb_utils.h"' -i src/postgres/src/pl/plpython/plpy_procedure.c
# if using python > 3.9 remove #include <compile.h> and  #include <eval.h> from src/postgres/src/pl/plpython/plpython.h
RUN sed -e '/#include <compile.h>/d' -e '/#include <eval.h>/d' -i src/postgres/src/pl/plpython/plpython.h
# add '--with-python', to python/yugabyte/build_postgres.py under the configure_postgres method
RUN sed -e "/'\.\/configure',/a\                '--with-python'," -i python/yugabyte/build_postgres.py
# Build and package the release
RUN YB_CCACHE_DIR="$HOME/.cache/yb_ccache" ./yb_build.sh -j$(nproc) --clean-all --build-yugabyted-ui --no-linuxbrew --clang15 -f release
RUN chmod +x bin/get_clients.sh bin/parse_contention.py bin/yb-check-consistency.py
RUN YB_USE_LINUXBREW=0 ./yb_release --force
WORKDIR /
RUN mv /yugabyte-db/build/yugabyte*.tar.gz /yugabyte.tgz

```

With this in `Dockerfile` I run the following to build the release into a Docker image:
```sh
docker build -t yb-with-python --build-arg "YB_TAG=2.19.3.0-b28" .

```
The release is available as `/yugabyte.tgz` and I'll test it in the image itself:
```sh
docker run --rm -it yb-with-python bash

tar -xvf /yugabyte.tgz
yugabyte-2.19.3.0/bin/yugabyted start

yugabyte-2.19.3.0/bin/ysqlsh -h $(hostname)

create extension plpython3u;

```

I'll try with an HTTP call to http://ipinfo.io and then need to have the `requests` module installed:

```sql
\! python -m pip install requests

CREATE FUNCTION ipinfo (path text default '/')
RETURNS text as $$
import requests
xxx
return requests.get(f'http://ipinfo.io/{path}').content.decode()
$$ LANGUAGE plpython3u;

select ipinfo();
select ipinfo('city');
```

Here is the result:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v3m3e89y1f4u6hqjorsv.png)

To use PL/Python you need to build it and be sure to deploy it in an environment where you have all dependencies (`python3` and the python modules). This is not specific to YugabyteDB but is how PostgreSQL extensions work.