Install PL/Python on Windows for PostgreSQL 12

Question

I've been working on FHIR for a project and we are using PostgreSQL as a database. While reading docs, I've come to know about PL/Python and decided to give it a shot but I am unable to install the python extension.

When I run the command CREATE EXTENSION pypthon3u; I get the following error

Could not load library "C:/Program Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not be found.

I've checked this SO answer but it couldn't help.

My PostgreSQL version: PostgreSQL 12.2, compiled by Visual C++ build 1914, 64-bit

Installed Python version: 3.7.7 (64 Bit)

OS Info: Windows 10 Enterprise Version 1909 OS Build 18363.657

For me, it looks like incorrect version of Python but I'm installing python 3.7.* version against which PostgreSQL is compiled as specified in doc\installation-notes.html inside the install directory.

Any help will be appreciated.

Naturalistic

unfortunately, I installed it 1 or two months ago so I'm not sure but I have this file (postgresql-12.2-1-windows-x64.exe) used to install DB. – Naturalistic

PL/Python is not installed. You have to install the binaries before you can create the extension. – Nepil

Can you please guide me on how to install binaries? – Naturalistic

@LaurenzAlbe Is this is what you are talking about? – Naturalistic

That looks like the EDB installer. If you use that, and have it launch Stack Builder, and have that install the language pack, it should install its own python. Is that what you did, or did you install python from elsewhere? – Hatchery

I downloaded python from its official site Python link – Naturalistic

Answer 1

Even if you use the EDB installer's Stack Builder to install Python, you still have to follow the instructions to "ensure they are included in the PATH variable under which the database server will be started". I had to do this at the system level, as I can't find a way to set the PATH for individual services.

And then you also need to set PYTHONPATH as well, which seems to be undocumented.

So I ended up adding c:\edb\languagepack\v1\Python-3.7 to PATH and creating PYTHONPATH with c:\edb\languagepack\v1\Python-3.7\Lib.

Hatchery

I didn't use the EDB installer. As per your recommendation, I've added PYTHONPATH to the path and as user variable but still getting the same error. – Naturalistic

PYTHONPATH and PATH are two separate variables, you don't set one to the other, you set them separately. In my hands, setting just PATH was enough to allow the extension to be created, but then it didn't work correctly until PYTHONPATH was set. – Hatchery

Yeah, I know both are different. I added python to the path, but it didn't work. Remember, I'm not using EDB and installed python from their official site. – Naturalistic

then I added PythonPath but no luck. – Naturalistic

I've reinstalled PostgreSQL and installed python using EDB language pack. c:\edb\languagepack\v1\Python-3.7 to PATH and added PYTHONPATH with c:\edb\languagepack\v1\Python-3.7\Lib but still getting the same error. – Naturalistic

To add. The Postgres installation notes for ver.15 is incorrect. Python3.10 not 3.9 is required. Using dependency walker as per this solution identifies the correct Python version to install and register in system environment variables: #21002390 – Catling

Answer 2

I had to add directory containing plpython3.dll do system/user variables (windows).

Baptistery

Answer 3

I was trying the same. Though I am no expert and my errors were a bit different, here are a few things to check:

• under share\extension\, there should be plpython3u.control and plpython3u--1.0.sql to be able to do CREATE EXTENSION plpython3u;
• ensure you are not having any typo in the command above.
• under lib\, there should be a plpython3.dll. This one is possibly created after the above step.
• make sure lib\ is in your PATH.
• PYTHONHOME should be set in either system/user environment, or should be set before the server start. It seems the version is not as much important as having this name set. plpython3.dll seems to look for a python39.dll but I could use it with a 3.8 installation.

I used a zipped version of Postgres, so the following is my way to run the server.

set PATH=%PATH%;d:\pgsql\bin\;d:\pgsql\lib\
set PYTHONHOME=c:\DevTools\Python\Python38
pg_ctl -D d:\pgsql\pgdata -l logfile start

PS: without PYTHONHOME, the server crashes. the server itself or psql restarts the server but I am not sure about pgAdmin.

Longerich

Answer 4

Adding to the Path wont help, the Database is going to be ran under the Postgres user so it does no good to add it to your path because the DB runner will not be you

Portsalut