2

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?

Pedro Pablo Severin Honorato
  • 377
  • 3
  • 16
  • "I checked if the plpython3.dll file is there." What was the outcome of that check? – jjanes Jun 09 '20 at 11:33
  • How did you install PostgreSQL in the first place? Using configure doesn't make much sense unless you installed from source to begin with. – jjanes Jun 09 '20 at 11:34
  • @jjanes plpython3.dll is present in the PC. I installed postgresql using the installer that can be downloaded from postgres's website. – Pedro Pablo Severin Honorato Jun 09 '20 at 13:15
  • 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? – jjanes Jun 09 '20 at 14:38

2 Answers2

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

Steve Elster
  • 21
  • 1
  • 3
0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi @LaurenzAlbe. The plpython3.dll file is present in the directory. I have python 3.7.4 installed in the PC – Pedro Pablo Severin Honorato Jun 09 '20 at 13:14
  • Is Python installed so that the Python DLL is on the `PATH`? – Laurenz Albe Jun 09 '20 at 13:39
  • 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 Jun 09 '20 at 13:58
  • How can I check if python DLL is on the path? And that python DLL is one that postgres uses? – Pedro Pablo Severin Honorato Jun 09 '20 at 14:27
  • You'd need a dependency checker like [dependency walker](https://www.dependencywalker.com/). 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). – Laurenz Albe Jun 09 '20 at 14:31
  • Ok. I downloaded dependecy walker. Now I should check the dependecies of plpython3.dll or python3.dll? Both are in different folders. The first one is in postgresql's lib folder and the other one is in anaconda's folder. There is also another file called "python3.dll" that is in this route: 'PostgreSQL\12\pgAdmin4\venv\Scripts\python3.dll' – Pedro Pablo Severin Honorato Jun 09 '20 at 15:08
  • The first one, `plpython3.dll`. The second has to be in a directory that is part of `PATH`. – Laurenz Albe Jun 09 '20 at 15:11
  • Okay. It shows me a lot of stuff. Just under PLPYTHON3.DLL name are 2 DLL and 1 .EXE with a circled yellow "?" sign. The names are LIBINTL-8.DLL, PYTHON37.DLL and POSTGRES.EXE. What should I do next? – Pedro Pablo Severin Honorato Jun 09 '20 at 15:16
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/215599/discussion-between-pedro-pablo-severin-honorato-and-laurenz-albe). – Pedro Pablo Severin Honorato Jun 09 '20 at 15:16
  • Right, the `python37.dll` must be the problem. You need this library on the `PATH`. – Laurenz Albe Jun 09 '20 at 15:24
  • And how do I set or add that library on the PATH? – Pedro Pablo Severin Honorato Jun 09 '20 at 15:29
  • [Look here](https://superuser.com/search?q=%5Bwindows%5D+set+path). – Laurenz Albe Jun 09 '20 at 15:33
  • Ok...I followed this [video](https://www.youtube.com/watch?v=Y2q_b4ugPWk), I think its the same, right? The path was `C:\Users\developer\Anaconda3`, so I added that to the `PATH` variable, as shown in the video. Is this correct? If it is, do I have to do next in order to use python in postgres? (its a 2 min video btw) – Pedro Pablo Severin Honorato Jun 09 '20 at 15:52
  • I actually created a variable `PYTHON_HOME = C:\Users\developer\Anaconda3` and added to the path. Now I need to restart the PC or Postgres? – Pedro Pablo Severin Honorato Jun 09 '20 at 16:15
  • I guess so, since it is Windows. Don't forget to say your ritual prayer to Bill Gates while rebooting. – Laurenz Albe Jun 09 '20 at 17:44
  • I restarted postgres and I'm getting the same error `ERROR: could not load library "C:/Program Files/PostgreSQL/12/lib/plpython3.dll": The specified module could not be found.`. I tried what this [post](https://stackoverflow.com/questions/39800075/error-during-create-extension-plpython3u-on-postgresql-9-6-0?rq=1) says (putting python37.dll in system32 folder) and I actually could execute `CREATE EXTENSION plpython3u`, but when trying to create the same function above, the server crashes and I get this message `The connection to the server was lost.` – Pedro Pablo Severin Honorato Jun 09 '20 at 17:56
  • I also executed `select * from pgpltemplate` and the plpython3u item says `False` in the `tmplname` column and `False` in the `tmpdbacreate` column – Pedro Pablo Severin Honorato Jun 09 '20 at 17:58
  • Reboot the machine is my recommendation. – Laurenz Albe Jun 10 '20 at 05:27