28

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!

questionto42
  • 7,175
  • 4
  • 57
  • 90
Paul Angelno
  • 391
  • 1
  • 4
  • 16
  • *I've tried installing additional packages*. Which additional packages exactly? – Craig Ringer Sep 29 '14 at 02:03
  • *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. – Craig Ringer Sep 29 '14 at 02:04
  • 1
    I believe I used `sudo apt-get install postgresql-contrib postgresql-plpython` – Paul Angelno Sep 29 '14 at 02:09
  • ... 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). – Craig Ringer Sep 29 '14 at 02:20

3 Answers3

17

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/ .

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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. – Paul Angelno Oct 01 '14 at 09:29
  • 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. – Peter Krauss Dec 03 '19 at 16:07
  • .. And there are no `apt` option for `"/usr/share/postgresql/12/extension/plpythonu.control"` – Peter Krauss Dec 03 '19 at 16:09
  • @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. – Craig Ringer Dec 06 '19 at 04:10
17

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

apt-get update && apt-get install postgresql-plpython3-11
andilabs
  • 22,159
  • 14
  • 114
  • 151
  • 1
    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 – Peter Krauss Dec 03 '19 at 16:03
  • Workingo for `CREATE EXTENSION plpython3u` – Peter Krauss Dec 03 '19 at 16:11
  • 1
    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? – Reinsbrain May 11 '20 at 07:01
  • 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.'](https://stackoverflow.com/questions/67852675/postgresql-13-python-3-7-9-plpython3u-psql-server-closed-the-connection-u). – questionto42 Aug 20 '21 at 19:41
  • `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. – questionto42 Aug 31 '21 at 13:42
6

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

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

  2. 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"?

  1. sudo su - postgres

  2. psql

  3. CREATE EXTENSION plpython3u;

  4. Verify with command:

select * from pg_language;
questionto42
  • 7,175
  • 4
  • 57
  • 90
Brian Blank
  • 91
  • 1
  • 3
  • 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.'](https://stackoverflow.com/questions/67852675/postgresql-13-python-3-7-9-plpython3u-psql-server-closed-the-connection-u). – questionto42 Aug 26 '21 at 19:31
  • 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)`! – questionto42 Aug 31 '21 at 18:21
  • 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?](https://stackoverflow.com/questions/69013111/linux-how-to-install-a-python-package-so-that-it-is-found-by-the-already-workin). Please answer if you know how. – questionto42 Sep 01 '21 at 13:41