PostgreSQL: how to install plpythonu extension

Question

I'm running PostgreSQL 9.3.1 on Ubuntu 12.04.4. I'd like to use the plpython language extension but I get an error when I try to use it I get:

ERROR: language "plpythonu" does not exist

When I try to create the extension:

CREATE EXTENSION plpythonu

I get ERROR: could not access file "$libdir/plpython2": No such file or directory After much searching and digging through blog posts, I've tried installing additional packages, and have copied all the plpython files from /usr/share/postgresql/9.1/extension to /opt/bitnami/postgresql/share/extension where PostgreSQL seems to be looking for them. That at least got me to a point at which PostgreSQL actually sees the available extensions. When I run:

SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name LIKE('plpy*')

I get :

    name    | default_version | installed_version 
------------+-----------------+-------------------
 plpython2u | 1.0             | 
 plpython3u | 1.0             | 
 plpythonu  | 1.0             | 

There are still no plpython libraries that I can see in /opt/bitnami/postgresql/lib. Can anybody help me get through remaining steps to make the extension work? Thanks in advance!

Kekkonen

I've tried installing additional packages. Which additional packages exactly? – Platen

copied all the plpython files from /usr/share/postgresql/9.1/extension to /opt/bitnami/postgresql/share/extension. Don't do that! You're lucky PostgreSQL has sanity checks to prevent that from loading incompatible modules and possibly corrupting process memory. – Platen

I believe I used sudo apt-get install postgresql-contrib postgresql-plpython – Kekkonen

... and how did you install bitnami PostgreSQL? (Note that 9.3.1 has some serious bugs, so you should promptly upgrade if you have data in it that you care about). – Platen

Answer 1

You're using a PostgreSQL package from Bitnami, in /opt. It's not clear if you installed this with apt-get or via an installer script/program, but in either case it's not the same PostgreSQL as what's in the Ubuntu postgresql package.

Installing postgresql-plpython won't do you any good, because you're installing PL/Python support for a different PostgreSQL install than the one you're actually using.

You'll need to use the same installation method you originally used to install the Bitnami PostgreSQL to add PL/Python support, if it's available. It might not be provided by Bitnami.

Otherwise, if you're not too attached to using Bitnami's PostgreSQL, you could use the recommended packages from http://apt.postgresql.org/ .

Platen

Thanks, Craig. I checked with the Bitnami community and unfortunately they don't support the PL/Python extension in their stacks yet so I'll do what you suggest. – Kekkonen

This answer need some upgrade for Ubuntu 18 LTS. sudo apt install postgresql-plpython do nothing, it say "Package postgresql-plpython is a virtual package provided by: postgresql-plpython-9.6, postgresql-plpython-9.7, ... postgresql-plpython-11`... So, important in the answer to say "how to select the correct one". There aro also python3, another selection problem. – Lynwoodlynx

.. And there are no apt option for "/usr/share/postgresql/12/extension/plpythonu.control" – Lynwoodlynx

@PeterKrauss That's a component of the PostgreSQL 12 plpython extension. It's unclear how you managed to install PostgreSQL 12 but not have plpython for PostgreSQL 12 available in your repositories. Post a new, separate question. – Platen

Answer 2

for postgres 11.2 (Debian based) I needed to install:

apt-get update && apt-get install postgresql-plpython3-11

Novokuznetsk

I used apt install postgresql-plpython3-12 on pg 12 and Ubuntu 18.... but on SQL the CREATE EXTENSION plpythonu; say ERROR: could not open extension control file "/usr/share/postgresql/12/extension/plpythonu.control": No such file or directory – Lynwoodlynx

Workingo for CREATE EXTENSION plpython3u – Lynwoodlynx

what happens when you are upgrading an older database to PG11 or PG12 with plpythonu functions? Do functions also need to change LANGUAGE from plpythonu to plpython3u? – Digester

Can anyone confirm that not only CREATE EXTENSION plpython3u works with this, but also running CREATE OR REPLACE FUNCTION return_version() RETURNS VARCHAR AS $$ import sys return sys.version $$ LANGUAGE plpython3u;? Since that does not work on Windows, see PostgreSQL 13 + Python 3.7.9 + plpython3u: 'psql: server closed the connection unexepectedly.' + 'The application has lost the database connection.'. – Fusibility

sudo apt-get update && apt-get install postgresql-plpython3-13 outputs: ... Hit:6 http://security.ubuntu.com/ubuntu focal-security InRelease Reading package lists... Done and then the errors: E: Could not open lock file /var/lib/dpkg/lock-frontend - open (13: Permission denied) and E: Unable to acquire the dpkg frontend lock (/var/lib/dpkg/lock-frontend), are you root?. I could get rid of this only by adding postgresql-contrib, see @BrianBlank's answer. – Fusibility

Answer 3

I'm running Raspbian 10 (buster) / Linux raspberrypi 4.19.97-v7+ #1294 and ran the following commands to install PL/Python 3 on PostgreSQL 11.7.

1. Identify which versions are available for install:

pi@raspberrypi:~/$ sudo apt-cache search ".*plpython3.*"
postgresql-plpython3-11 - PL/Python 3 procedural language for PostgreSQL 11

2. sudo apt-get install postgresql-contrib postgresql-plpython3-11

3. sudo systemctl start postgresql (or use enable to start this at every startup, see Getting started with PostgreSQL on Linux) on stand-alone Linux or sudo service postgresql start (on WSL2).

Else, you would get the error:

psql: error: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

4. sudo su - postgres

5. psql

6. CREATE EXTENSION plpython3u;

7. Verify with command:

select * from pg_language;

Knar

Can anyone confirm that not only CREATE EXTENSION plpython3u and select * from pg_language; works with this, but also running CREATE OR REPLACE FUNCTION return_version() RETURNS VARCHAR AS $$ import sys return sys.version $$ LANGUAGE plpython3u;? Since the latter does not work on Windows, the two former do, see PostgreSQL 13 + Python 3.7.9 + plpython3u: 'psql: server closed the connection unexepectedly.' + 'The application has lost the database connection.'. – Fusibility

I can confirm this now. I could get the function created (output: CREATE FUNCTION) and then I could run SELECT return_version(); to get the output: return_version ------------------------------------------ 3.8.10 (default, Jun 2 2021, 10:49:15) + [GCC 9.4.0] (1 row)! – Fusibility

I could not find a way to import still to be installed packages, though, see Linux: How to install a Python package so that it is found by the already working PostgreSQL 13 plpython3u extension?. Please answer if you know how. – Fusibility

Answer 4

If you are using psql-16 with Ubuntu you should use this command:

sudo apt install postgresql-plpython3-16

It worked for me.

Razo

If the package is always postgresql-plpython3-xx where xx is the PostgreSQL major version, I don't think we need an answer for every version. Proposing an edit to one of the other answers clarifyingithe naming convention would be more useful. – Smallclothes

I thought about that too, but I wasn't sure. – Spirt