CREATE LANGUAGE plpython3u - PostgreSQL 9.6

Published on Thu, Sep 14, 2017

This is one of the quickest blog am publishing :). I am publishing from my terminal as is of my testing to create language plpython3u.

Using trusted or untrusted distributions of python we can create plpython3u language in PostgreSQL. In my testing, am trying with SCL distribution(am not recommending, I tried for testing) of python3.3 to create language plpython3u.

Let’s begin creating language on a binary version of PostgreSQL 9.6 installation without any tweaking.

-bash-4.2$ psql  
psql.bin (9.6.4)  
Type "help" for help. 

postgres=\# CREATE LANGUAGEplpython3u;  
ERROR: could not load library "/opt/PostgreSQL/9.6/lib/postgresql/plpython3.so":libpython3.3m.so.1.0: cannot open shared object file: No such file ordirectory

Hmmm, “/opt/PostgreSQL/9.6/lib/postgresql/plpython3.so” looking for a library “libpython3.3m.so.1.0”. To confirm, run “ldd” command

-bash-4.2$ cd
/opt/PostgreSQL/9.6/lib/postgresql/  
-bash-4.2$ ldd plpython3.so  
linux-vdso.so.1 => (0x00007fff9db12000)  
libpython3.3m.so.1.0 => not found  
libc.so.6 => /lib64/libc.so.6 (0x00007fe75e42f000)  
/lib64/ld-linux-x86-64.so.2 (0x00007fe75ea27000)

Now, its clear we need libpython3.3m.so.1.0 to create plpython3u language.

Lets get started as a root user to install python3.3 from SCL repo by enabling it.


#yum install centos-release-scl
#yum install python33

After installing, find for a library “libpython3.3m.so.1.0” required by plpython3.so


[root@tools ~]# find / -name libpython3.3m.so.1.0
/opt/rh/python33/root/usr/lib64/libpython3.3m.so.1.0

Cool. To make use of Python3 bundle switch as a postgres user and set the environment variable PYTHONPATH, PYTHONHOME, PATH and LD_LIBRARY_PATH.


-bash-4.2$ export
PYTHONPATH=/opt/rh/python33/root/usr  
-bash-4.2$ export PYTHONHOME=/opt/rh/python33/root/usr  
-bash-4.2$ export LD\_LIBRARY\_PATH=/opt/rh/python33/root/usr/lib64:$LD_LIBRARY_PATH  
-bash-4.2$ export PATH=$PYTHONPATH:$PATH

Try running “ldd” on “/opt/PostgreSQL/9.6/lib/postgresql/plpython3.so” again to check the libraries are properly picked.


-bash-4.2$ cd
 /opt/PostgreSQL/9.6/lib/postgresql/  
 -bash-4.2$ ldd plpython3.so  
 linux-vdso.so.1 => (0x00007fffe26ed000)  
 libpython3.3m.so.1.0 => /opt/rh/python33/root/usr/lib64/libpython3.3m.so.1.0 (0x00007fd31c205000)  
 libc.so.6 => /lib64/libc.so.6 (0x00007fd31be2d000)  
 libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fd31bc11000)  
 libdl.so.2 => /lib64/libdl.so.2 (0x00007fd31ba0d000)  
 libutil.so.1 => /lib64/libutil.so.1 (0x00007fd31b809000)  
 libm.so.6 => /lib64/libm.so.6 (0x00007fd31b507000)  
 /lib64/ld-linux-x86-64.so.2 (0x00007fd31c89c000)

Nice. We are all set. Let’s restart the server and create the language plpython3u.


-bash-4.2$ pg\_ctl restart
server starting

-bash-4.2$ psql
psql.bin (9.6.4)
Type "help" for help.

postgres=\# CREATE LANGUAGE plpython3u;
CREATE LANGUAGE

Very nice… One last step, lets test plpython3u language by creating a sample function.


postgres=\# CREATE OR REPLACE FUNCTION maxme
(a integer, b integer) RETURNS integer AS  
$$  
if a > b:  
return a  
return b  
$$ LANGUAGE plpython3u;  
CREATE FUNCTION  

postgres=\# SELECT maxme(1,
2);  
memax  
-------  
2  
(1 row)  

Good. Enjoy!!

Please feel free to write me an email to share your comments & thoughts.

Raghav