windows - PostgreSQL 13 + Python 3.7.9 + plpython3u: 'psql: server closed the connection unexepectedly.' + 'The application has lost the database connection.'

Question

I have added all of the details I could find, with all of the links, and there is no way to get plpython3u to work on Windows in PostgreSQL 13, it seems.

Better do not read through this long question and rather just jump to the answer: not to use Windows PostgreSQL when you need plpython3u. This question has been opened long enough, no solution in sight.

Perhaps a higher PostgreSQL version for Windows will solve this, then please answer.

---

Spin-off

This is a spin-off from

Can't “install” plpython3u - postgresql and all of its comments

and from

PosgreSQL 11 lost connection when i'm trying to create function with plpython3u [WIN10, pgAdmin4 3.5].

Steps of errors and solutions up to now

I have taken these steps which were totally scattered across Stack Overflow:

Step 0

If you run a sql that uses the language plpython3u without it being installed, you get

ERROR: language "plpython3u" does not exist HINT: Use CREATE EXTENSION to load the language into the database.

SQL state: 42704

Related:

• Can't “install” plpython3u - postgresql

Step 1

At error

ERROR: could not load library "C:/Program Files (x86)/PostgreSQL/13/lib/plpython3u.dll": The specified module could not be found.

SQL state: 58P01

look up C:Program FilesPostgreSQL13docinstallation-notes.html to find the needed Python version to be installed for the installed PostgreSQL version.

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 the 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

Thus, Python 3.7 is needed.

Credits go to:

• could not load library plpython3.dll --> comment: Where to find this information ? Like for plpython3u which python version is required ?, answered Jul 17 '20
• Error during: CREATE EXTENSION plpython3u; on PostgreSQL 9.6.0, edited Oct 2 '20

Related:

• Installing plpythonu on Windows
• Install PL/Python on Windows for PostgreSQL 12

Step 2

Install Python version using the webinstaller of Python Releases for Windows

The most recent sub-version 3.7.10 does not have any files in the list of stable releases and I am too lazy to install Python from source on Windows. The source code of v3.7.10 is available here Looking for a specific release?, for anyone who wants to try):

Python 3.7.10 - Feb. 15, 2021

Note that Python 3.7.10 cannot be used on Windows XP or earlier.

No files for this release.

Explanation copied from How to build Python 3.4.6 from source?

The Python 3.7 branch is in security fixes only mode. This means that only security fixes will be accepted on this branch, no more non-critical bug fixes. New releases on this branch are source-only, no binaries will be provided.

See the official announcement.

If you really need a python 3.7.10 binary for windows, you will have to compile it yourself.

Cannot install plpython for postgres 12 recommends to install a specific version from source:

you want to use a specific python version > use source and compile it

Again, since I am lazy, I take the most recent stable release of 3.7, which is sub-version 3.7.9, and this should be no problem following the remark, as you seem to be free to choose the sub-version:

Try version python-3.4.0.amd64 for windows 64bit or other versions from this Python 3.4.0 downloads Link

From: could not load library plpython3.dll

As I said, I am too lazy to take the effort of compiling the binaries of v3.7.10 on Windows when v3.7.9 is available, thus:

Python 3.7.9 - Aug. 17, 2020

Note that Python 3.7.9 cannot be used on Windows XP or earlier.

Download Windows help file
Download Windows x86-64 embeddable zip file
Download Windows x86-64 executable installer
Download Windows x86-64 web-based installer
Download Windows x86 embeddable zip file
Download Windows x86 executable installer
Download Windows x86 web-based installer

I install "Download Windows x86-64 web-based installer" (side-note: you cannot change the installation path, they seem to force you to use this; to reach it quickly, in Windows Explorer, type in the path %appdata% --> go to parent folder "appdata" --> then to "local" --> "programs" --> "python" to quickly get there) and check the box for adding the PATH variables as well.

You will have a new entry in your user environment variable "PATH" and you may check this, but you do not need to:

C:UsersMY_USERAppDataLocalProgramsPythonPython37Scripts

and

C:UsersMY_USERAppDataLocalProgramsPythonPython37

Credits go to:

• could not load library plpython3.dll, answered Jan 31 '18

Step 3

When executing

CREATE EXTENSION plpython3u;

in the query tool of PostgreSQL pgAdmin4, I get the error:

could not load library "C:/Program Files/PostgreSQL/13/lib/plpython3u.dll": The specified module could not be found

Go to your Python 3.7 installation folder, in my case

C:UsersMY_USERAppDataLocalProgramsPythonPython37

and copy "python37.dll" from there to

C:WindowsSystem32

by confirming that you have admin rights.

Now execute again and it will work:

CREATE EXTENSION plpython3u;

Credits go to:

• Error during: CREATE EXTENSION plpython3u; on PostgreSQL 9.6.0, answered Sep 18 '17
• Is there any recipe to successfully install PLPython in Postgresql 9.3 64bit or 32bit on Windows 64 bits?, answered Feb 8 '14
• Cannot install plpython for postgres 12, answered Oct 2 '20

Related questions:

• PostgreSQL unable to create plpythonu extension
• How to install PL/Python on PostgreSQL 9.3 x64 Windows 7?
• i'm facing issues to create a postgresql plpython3u extension

Step 4 (optional)

SELECT * FROM pg_extension;

Output:

old    | extname       | extowner | extrelocatable | extversion | extversion | extconfig | extcondition
"13428"| "plpgsql"     | "10"     | "11"           | false      | "1.0"      | [null]    | [null]
"16776"| "plpython3u"  | "10"     | "11"           | false      | "1.0"      | [null]    | [null]

And another check with:

SELECT * FROM pg_language;

Output:

  lanname   | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
------------+----------+---------+--------------+---------------+-----------+--------------+--------
 internal   |       10 | f       | f            |             0 |         0 |         2246 |
 c          |       10 | f       | f            |             0 |         0 |         2247 |
 sql        |       10 | f       | t            |             0 |         0 |         2248 |
 plpgsql    |       10 | t       | t            |         12279 |     12280 |        12281 |
 plpython3u |       10 | t       | f            |         40963 |     40964 |        40965 |
(5 rows)

Now the available extensions (that is, all possible extensions that can be installed) also show installed_version = 1.0 for the plpython3u extension:

SELECT * FROM pg_available_extensions WHERE name LIKE '%python%' ORDER BY name;

Output:

or the output when running the same in psql:

    name    | default_version | installed_version |                  comment
------------+-----------------+-------------------+-------------------------------------------
 plpython3u | 1.0             | 1.0               | PL/Python3U untrusted procedural language
(1 Zeile)

<

Answer 1

Workaround: plpython3u does work on Linux! So use it on Linux.

This last paragraph is not part of the question and just lists some steps on Linux as an alternative in the meantime when the Windows plpython3u installation does not work.

Docker:

• On Windows, install Docker Desktop (recommended) or use Docker on WSL2. Elsewise, just install Docker directly on Linux.

• A typical postgres Docker setup that you can easily change to a more recent version can be found at Docker PostgreSQL 9.6 - installing extension plpython3u (clashing with quantile extension).

• Docker has the problem that you need extra tricks so that the database is saved even if you remove the container, like:

  • pg_dump / pb_restore / psql ... > / psql... < to backup on the local Linux disk and then restore the database from a mounted volume of your choice, or
  • a Web Server to save your db permanently.

• You might also catch a first glimpse from threads like PostgreSQL on Docker: How to install and run python dependencies under plpython3u?, to start with, or take the official postgres image using docker-compose guide as the postgres base and extend it by plpython3.

• One main trick in one container that failed to use plpython3u was to add SymLinks instead of hardcoded installation paths, see Add plpython3 Extension to Postgres/timescaledb Alpine Docker Image. This worked for me. Using this alpine TimescaleDB Dockerfile, I could use plpython3u! Caveat of this old Python 3.6 version in this container: I could not install the needed packages for the kmeans test above which are pandas, scikit-learn and pickle, neither with pip nor with Poetry. And it seems that this alpine container with Python 3.6 does not support pandas while Python 3.7 would: Installing pandas in docker Alpine. If the needed packages cannot be installed, plpython3u is of no value in this docker container. That is why you should use a more recent Docker image of PostgreSQL (or for example timescaleDB) so that you do not run into such legacy issues of Python 3.6 dependencies.

Standalone

You might also try an installation on standalone Linux when saving the data permanently and locally gets more important.

• There is an official guide for Linux installations at PostgreSQL Downloads and PostgreSQL Wiki Apt.
• This link shows how to install the extension: PostgreSQL: how to install plpythonu extension, using for example postgresql-plpython3-13 for postgres 13; in 9/2021, this installs Python 3.8.10. I can confirm that it works! I could create the plpython3u return_version() function of above and run it. If you need to import packages that need to be installed, see for example Linux: How to install a Python package so that it is found by the already working PostgreSQL 13 plpython3u extension?.

Guessed answer

With WSL, WSL2 and Docker Desktop, since years, Linux has become a friend of Windows. Windows seems to encourage this. The shift towards postgreSQL on Linux is probably the reason for the poor Windows support of plpython3u, recently. In the meantime, you should install it on Linux (standalone or in a Docker container).

But what might be wrong wrong with the Windows installation?

• As already said, Windows does not get the PostgreSQL attention that Linux gets. I guess that on Windows, I have to install PostgreSQL from source, together with the plpython extension and its dependencies, to get plpython3u to run properly.

• Perhaps, the normal Windows installer also does not support plpython just because of a mere technical detail: The query in the question above shows: PL/Python3U untrusted procedural language. It may not be allowed on a usual production system. For example, the Webserver service TimescaleForge (timescaleDB, based on PostgreSQL) have answered that they do not offer any plpython extension because of the security risk, even if the client asks for it. They rather offer trusted extensions for clear problems, not a full language that can do anything and is therefore a security risk. Obviously, you can use untrusted extensions when building from source, as TimescaleForge do with their own extensions.

• There might be the need to set a PATH variable as in the answer at “Module not found” when importing a Python package within a plpython3u procedure.

• Perhaps on Windows, the default version of Python must be changed somewhere before installation? This is just a very vague guess from a Linux installation using make, with Python settings in /etc/make.conf

• And finally:

It seems possible that plpython3u will run in PostgreSQL on Windows when the same installation tricks are used as have been used in this Dockerfile of the mentioned link above, Add plpython3 Extension to Postgres/timescaledb Alpine Docker Image, where plpython3u works:

RUN set -ex 
    && apk add --no-cache --virtual .plpython3-deps --repository http://nl.alpinelinux.org/alpine/edge/testing 
    postgresql-plpython3 
    && ln -s /usr/lib/postgresql/plpython3.so /usr/local/lib/postgresql/plpython3.so 
    && ln -s /usr/share/postgresql/extension/plpython3u.control /usr/local/share/postgresql/extension/plpython3u.control 
    && ln -s /usr/share/postgresql/extension/plpython3u--1.0.sql /usr/local/share/postgresql/extension/plpython3u--1.0.sql 
    && ln -s /usr/share/postgresql/extension/plpython3u--unpackaged--1.0.sql /usr/local/share/postgresql/extension/plpython3u--unpackaged--1.0.sql

Thus, .plpython3-deps and postgresql-plpython3 must be installed and the SymLinks must be added.

Perhaps, such SymLinks are already the main trick on Windows as well, though I could not get it to work with SymLinks in a quick test, see PostgreSQL on Windows: get “plpython3u” extension to run with the help of SymLinks?.