Can't "install" plpython3u - postgresql

Question

I'm trying to use python language in postgresql. Something like this:

create or replace function test(_a integer) returns integer as $$
if _a%2==0:
 return 'even'
elif _a%3==0:
 return 'mult of 3'
else:
 return _a
$$ language plpython3u

But when I run this, I get this error:

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

Then, I tried to create the extension of the python language by executing:

create extension plpython3u

Which tells me the following error:

ERROR: could not load library "C:/Program Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not be found.
SQL state: 58P01

I checked if the plpython3.dll file is there. Then I read something about modifying the postgresql configure file by compiling postgres from the source code and adding --with python (I found some of this here).

My problem is that I don't know how to actually do this. My OS is windows server 2019 64 bits, python version is 3.7.4 and postgresql version is 12.2-1 (pgadmin 4.18).

How can I solve this?

• jjanes: "I checked if the plpython3.dll file is there." What was the outcome of that check?
• jjanes: How did you install PostgreSQL in the first place? Using configure doesn't make much sense unless you installed from source to begin with.
• Pedro Pablo Severin Honorato: @jjanes plpython3.dll is present in the PC. I installed postgresql using the installer that can be downloaded from postgres's website.
• jjanes: Sounds like the EDB installer. Did you run the language pack portion of the installer? Did you set up PATH and PYTHON_HOME environment variables?

Answer 1

Getting python to work with postgres seems to be version or versions dependent. I currently have postgres 9.6. and I had installed python 3.9. I had previously installed the extension plpython3u in postgres but time had gone by and I moved to another computer. When I tried to run a procedure based on Python, I got an error. I downloaded dependency walker, from here: https://www.opcsupport.com/s/article/How-do-I-figure-out-why-my-DLL-is-failing-Microsoft-Dependency-Walker or here: https://www.dependencywalker.com/. When I opened up the dependency walker, I dragged the plpython3.dll into the dependency walker, the .dll is located here: F:\pg96\lib. I then got the following screen -- below -- that seemed to indicate I needed to install a Python 3.7. I downloaded Python 3.7 from here: https://www.python.org/downloads/windows/. And my python procedures now worked.

screenshot from dependency walker

• pelelter: Worked for me as well! Just had to reboot the server after doing it.
• JOMMMM: Go to your \PostgreSQL\xx\doc\installation-notes.html file. This file indicates the Python interpreter necessary version you need to install. As Steve recomended this worked for me. I'm in PostgreSQL ver 17 so this situation has been happening for several years now.

Answer 2

No need to build from source, which would require that you install a C compiler, which is non-trivial on Windows.

You never told us if python3.dll was present in the directory or not, so I'll assume it was there. Then the error would indicate that a shared library that python3.dll links with is missing, most likely from Python 3. Installing Python 3 will probably solve the problem.

• Pedro Pablo Severin Honorato: Hi @LaurenzAlbe. The plpython3.dll file is present in the directory. I have python 3.7.4 installed in the PC
• Laurenz Albe: Is Python installed so that the Python DLL is on the PATH?
• Pedro Pablo Severin Honorato: Hmmm I don't know, this path thing is kinda new to me. Sorry for the inconvenience :(. I installed python using anaconda and used default settings.
• Pedro Pablo Severin Honorato: How can I check if python DLL is on the path? And that python DLL is one that postgres uses?
• Laurenz Albe: You'd need a dependency checker like dependency walker. That enables you to examine python3.dll and find what libraries it requires and if they can be found or not. How you can modify the PATH should be easy to figure out with a web search (I don't have a Windows system near, and it varies from version to version).