11

I want to have python3 in my postgresql database for writing stored procedures.

Being in the psql client, when I enter the command create extension plpython3u I get the error:

couldn't open extension control file /usr/share/postgresql/9.1/extension/plpython3u.control : No such file or directory

I have checked, there are plpythonu.control and plpython2u.control in the directory, but not the one for version 3.

However I have installed the packages python3 and python3-postgresql (among others) from depot. I'm using Ubuntu 12.04, kernel 3.2.0.38, with postgresql 9.1 installed.

What should I install (or do) to have the plpython3u.control file on my machine and have python3 available in my database?

Improve this question

2 Answers 2

Reset to default
8

The package to install is postgresql-plpython3.

By way of dependencies, this will install postgresql-plpython3-9.1, which provides these files:

$ dpkg -L postgresql-plpython3-9.1
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-plpython3-9.1
/usr/share/doc/postgresql-plpython3-9.1/copyright
/usr/share/postgresql
/usr/share/postgresql/9.1
/usr/share/postgresql/9.1/extension
/usr/share/postgresql/9.1/extension/plpython3u--1.0.sql
/usr/share/postgresql/9.1/extension/plpython3u.control
/usr/share/postgresql/9.1/extension/plpython3u--unpackaged--1.0.sql
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/9.1
/usr/lib/postgresql/9.1/lib
/usr/lib/postgresql/9.1/lib/plpython3.so
/usr/share/doc/postgresql-plpython3-9.1/changelog.Debian.gz

and then you can do:

postgres=# create extension plpython3u;
CREATE EXTENSION
Improve this answer
3
  • Did you find out the package in which the file was thanks to a special linux command, or did you know it ?
    – Stephane Rolland
    Commented Mar 22, 2013 at 0:51
  • 1
    @Stephane: apt-cache search plpython. apt-cache show packagename is useful as well.
    – Daniel Vérité
    Commented Mar 22, 2013 at 13:56
  • I have the same problem whit PostgreSQL 12 in Ubuntu 19.10. I even used above command successfully but when update my Odoo server where there is plpythonu usage for creating extension I still get the error. Means it might not work for python2.
    – Ibrahim Rahimi
    Commented Jan 20, 2020 at 9:12
0

Check the spelling

You get this message whenever you create an extension that is not available. I had the same error when running the CREATE EXTENSION plpython3u with a spelling mistake.

Check the right Python version

One other idea is that you need the right Python version. The right version differs between Windows and Linux. Normally, you should not install the exact version yourself but rather use installers (Windows: EDB and Stack Builder --> Language pack, Linux: Follow PostgreSQL: how to install plpythonu extension.

Windows

In case of using a Windows installer, you need to install the right Python version that fits your PostgreSQL version and also set the PATH variables of Python (at least, that is needed on Windows as well, though this should be done automatically with the Python installation anyway).

On Windows, you look up C:\Program Files\PostgreSQL\13\doc\installation-notes.html to find the needed Python version to be installed for the installed PostgreSQL version. In my case, with PostgreSQL 13:

PostgreSQL 13

Installation Notes

Welcome to the PostgreSQL 13 Installation Wizard.

Procedural Languages

The procedural languages pl/Perl, pl/Python, and pl/Tcl are included in this distribution of PostgreSQL. The server has been built using the LanguagePack community distributions of those language interpreters. To use any of these languages from within PostgreSQL, download and install the appropriate interpreters and ensure they are included in the PATH variable under which the database server will be started. The versions used are shown below - newer minor (bugfix) releases may also work, but have not been tested:

Perl 5.26
Python 3.7
Tcl 8.6

enter image description here

Thus, Python 3.7 is needed on Windows.

I took this from a Windows PostgreSQL + plpython3u installation overview at Stack Overflow PostgreSQL 13 + Python 3.7.9 + plpython3u: 'psql: server closed the connection unexepectedly.' + 'The application has lost the database connection.'.

Linux

On Linux, I could not find such an installation-notes.html file with the needed Python version as you find it on Windows.

Checking the Python version directly as a stored procedure in PostgreSQL with plpython3u already installed:

CREATE OR REPLACE FUNCTION return_version()
  RETURNS VARCHAR
AS $$
    import sys
    return sys.version
$$ LANGUAGE plpython3u;

Output:

CREATE FUNCTION

Test:

postgres=# SELECT return_version();
              return_version
------------------------------------------
 3.8.10 (default, Jun  2 2021, 10:49:15) +
 [GCC 9.4.0]
(1 row)

shows that for "PostgreSQL 13", "Python 3.8.10" gets installed. The Linux installer seems to get more attention than the Windows installer and might therefore have a more recent Python version.

Thus, in 9/2021, you need to have installed Python 3.8.10 to make this extension work. It should be automatically installed by the linux installer, though, but you can at least check with this whether you are on the right way, you should find it with:

whereis python3.8

Output:

python3: /usr/bin/python3.8 /usr/bin/python3 /usr/bin/python3.8-config /usr/lib/python3.8 /usr/lib/python3.9 /usr/lib/python3 /etc/python3.8 /etc/python3 /usr/local/lib/python3.8 /usr/include/python3.8 /usr/share/python3
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.