Skip to content Skip to sidebar Skip to footer
Home / Plpython / Postgresql / Postgresql 13 / Python 3.7 / Windows

Postgresql 13 + Python 3.7.9 + Plpython3u: 'psql: Server Closed The Connection Unexepectedly.' + 'the Application Has Lost The Database Connection.'

Post a Comment
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. OLD, since the accepted an

Solution 1:

Workaround for Windows and Postgres 13

I was finally able to make it work with Python 3.7.0. If anybody is looking for a solution they can install Python 3.7.0. Of course any new introduced in subsequent versions will not be available. I had to refactor my code to remove f-strings. Its a small trade-off for the convenience of using Python in stored procedures and functions.

PostgresSQL version: 13.4-2

Python version download link https://www.python.org/ftp/python/3.7.0/python-3.7.0-amd64.exe

Solution 2:

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.

Guessed answer (ALL WRONG; SEE ACCEPTED ANSWER INSTEAD)

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? which was rightfully deleted since the solution is much easier, see the other answer.

You may like these posts

Post a Comment for "Postgresql 13 + Python 3.7.9 + Plpython3u: 'psql: Server Closed The Connection Unexepectedly.' + 'the Application Has Lost The Database Connection.'"