cancel
Showing results for 
Search instead for 
Did you mean: 

unable to install pl/python extension

SOLVED
Highlighted
Level 2 Adventurer

unable to install pl/python extension

Hi,

I have installed PostgreSQL with the enterprisedb installer version 11.4.

PostgreSQL 11.4, compiled by Visual C++ build 1914, 64-bit

Now I want to install the python extension but I always get this error:

postgres=# create extension plpython3u;
ERROR: could not load library "C:/Program Files/PostgreSQL/11/lib/plpython3.dll": The specified module could not be found.

I have tried my own Python version (3.6.8) and an embeddable version (3.6.5) both did not work. In both cases I have set the PATH variable to the correct Python version. The plpython3.dll is also available. Right now I have installed the LanguagePack provided with the StackBuilder also no success. I have set the PYTHONHOME variable and set PATH to the languagepack python version.

In all cases a dependency analyze tool finds the needed python36.dll.

 

I am running Windows 10.

 

Regards,

localhost

1 ACCEPTED SOLUTION

Accepted Solutions
EDB Team Member

Re: unable to install pl/python extension

Hi Paul,

 

I request you follow below steps to resolve the issue.

1. Copy the mentioned DDL from the system32 folder to its original location.

2. Start the command prompt in "Run as administrator" option.

3. SET the PYTHON_HOME and PATH variable as below 

 

 

c:\Program Files\PostgreSQL\11\bin>set PYTHON_HOME=c:\edb\languagepack-11\x64\Python-3.6

c:\Program Files\PostgreSQL\11\bin>set PATH=c:\edb\languagepack-11\x64\Perl-5.26\bin;c:\edb\languagepack-11\x64\Python-3.6;c:\edb\languagepack-11\x64\Tcl-8.6\bin;%PATH%

 

 

4. Restart the Database server 

 

C:\Windows\system32>cd "c:\Program Files\PostgreSQL\11\bin"

c:\Program Files\PostgreSQL\11\bin>pg_ctl.exe -D ..\data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-08-16 16:27:56.304 +0530 [8992] LOG:  listening on IPv6 address "::", port 5432
2019-08-16 16:27:56.304 +0530 [8992] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-08-16 16:27:56.366 +0530 [8992] LOG:  redirecting log output to logging collector process
2019-08-16 16:27:56.366 +0530 [8992] HINT:  Future log output will appear in directory "log".
 done
server started

 

 

5. Create the extension

 

c:\Program Files\PostgreSQL\11\bin>psql.exe -U postgres
Password for user postgres:
psql (11.5)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# select version ();
                          version
------------------------------------------------------------
 PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit
(1 row)


postgres=# create extension plpython3u;
CREATE EXTENSION

postgres=# \dx plpython3u
                         List of installed extensions
    Name    | Version |   Schema   |                Description
------------+---------+------------+-------------------------------------------
 plpython3u | 1.0     | pg_catalog | PL/Python3U untrusted procedural language
(1 row)


postgres=# \dx+ plpython3u
      Objects in extension "plpython3u"
             Object description
---------------------------------------------
 function plpython3_call_handler()
 function plpython3_inline_handler(internal)
 function plpython3_validator(oid)
 language plpython3u
(4 rows)

 

 

Hope this helps. 

Regards,
Dhananjay

View solution in original post

7 REPLIES 7
Level 2 Adventurer

Re: unable to install pl/python extension

I was able to fix this issue by myself after I have copied the python36.dll into the system32 folder even though it was the same dll which was lying inside of the added directory inside the PATH variable. It would be nice to get feedback on how I can do it with the PATH variable so I can delete the dll from the system32 directory.

 

But this lead to the following problem:

 

postgres=# create extension plpython3u;
ERROR:  extension "plpython3u" already exists
postgres=# CREATE FUNCTION pymax (a integer, b integer)
postgres-#   RETURNS integer
postgres-# AS $$
postgres$#   if a > b:
postgres$#     return a
postgres$#   return b
postgres$# $$ LANGUAGE plpython3u;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

I called create extension plpython3u; just to show it is already installed. 

 

This is the log file:

2019-08-14 14:05:03.213 GMT [2544] LOG:  database system was shut down at 2019-08-14 14:05:01 GMT
2019-08-14 14:05:03.276 GMT [23076] LOG:  database system is ready to accept connections
Fatal Python error: Py_Initialize: unable to load the file system codec
ModuleNotFoundError: No module named 'encodings'

Current thread 0x00006ce4 (most recent call first):
2019-08-14 14:05:45.025 GMT [23076] LOG:  server process (PID 27136) was terminated by exception 0xC0000409
2019-08-14 14:05:45.025 GMT [23076] DETAIL:  Failed process was running: CREATE FUNCTION pymax (a integer, b integer)
	  RETURNS integer
	AS $$
	  if a > b:
	    return a
	  return b
	$$ LANGUAGE plpython3u;
2019-08-14 14:05:45.025 GMT [23076] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2019-08-14 14:05:45.025 GMT [23076] LOG:  terminating any other active server processes
2019-08-14 14:05:45.026 GMT [19356] WARNING:  terminating connection because of crash of another server process
2019-08-14 14:05:45.026 GMT [19356] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-08-14 14:05:45.026 GMT [19356] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2019-08-14 14:05:45.068 GMT [13104] FATAL:  the database system is in recovery mode
2019-08-14 14:05:45.083 GMT [23076] LOG:  all server processes terminated; reinitializing
2019-08-14 14:05:45.146 GMT [27744] LOG:  database system was interrupted; last known up at 2019-08-14 14:05:03 GMT
2019-08-14 14:05:45.843 GMT [27744] LOG:  database system was not properly shut down; automatic recovery in progress
2019-08-14 14:05:45.847 GMT [27744] LOG:  redo starts at 0/1B7D320
2019-08-14 14:05:45.850 GMT [27744] LOG:  invalid record length at 0/1B8E7D0: wanted 24, got 0
2019-08-14 14:05:45.850 GMT [27744] LOG:  redo done at 0/1B8E798
2019-08-14 14:05:45.850 GMT [27744] LOG:  last completed transaction was at log time 2019-08-14 14:05:34.39136+00

 Even the steps from the LanguagePack Guide does not work:

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/language-pack-guide...

EDB Team Member

Re: unable to install pl/python extension

Hi Localhost,

 

Hope you are doing well!

 

We are working on your reported issue and we will update you soon.

 

Please let us know in case of any concerns or issues.

 

Regards

Dhananjay

Regards,
Dhananjay
EDB Team Member

Re: unable to install pl/python extension

Hi Paul,

 

I request you follow below steps to resolve the issue.

1. Copy the mentioned DDL from the system32 folder to its original location.

2. Start the command prompt in "Run as administrator" option.

3. SET the PYTHON_HOME and PATH variable as below 

 

 

c:\Program Files\PostgreSQL\11\bin>set PYTHON_HOME=c:\edb\languagepack-11\x64\Python-3.6

c:\Program Files\PostgreSQL\11\bin>set PATH=c:\edb\languagepack-11\x64\Perl-5.26\bin;c:\edb\languagepack-11\x64\Python-3.6;c:\edb\languagepack-11\x64\Tcl-8.6\bin;%PATH%

 

 

4. Restart the Database server 

 

C:\Windows\system32>cd "c:\Program Files\PostgreSQL\11\bin"

c:\Program Files\PostgreSQL\11\bin>pg_ctl.exe -D ..\data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-08-16 16:27:56.304 +0530 [8992] LOG:  listening on IPv6 address "::", port 5432
2019-08-16 16:27:56.304 +0530 [8992] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-08-16 16:27:56.366 +0530 [8992] LOG:  redirecting log output to logging collector process
2019-08-16 16:27:56.366 +0530 [8992] HINT:  Future log output will appear in directory "log".
 done
server started

 

 

5. Create the extension

 

c:\Program Files\PostgreSQL\11\bin>psql.exe -U postgres
Password for user postgres:
psql (11.5)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# select version ();
                          version
------------------------------------------------------------
 PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit
(1 row)


postgres=# create extension plpython3u;
CREATE EXTENSION

postgres=# \dx plpython3u
                         List of installed extensions
    Name    | Version |   Schema   |                Description
------------+---------+------------+-------------------------------------------
 plpython3u | 1.0     | pg_catalog | PL/Python3U untrusted procedural language
(1 row)


postgres=# \dx+ plpython3u
      Objects in extension "plpython3u"
             Object description
---------------------------------------------
 function plpython3_call_handler()
 function plpython3_inline_handler(internal)
 function plpython3_validator(oid)
 language plpython3u
(4 rows)

 

 

Hope this helps. 

Regards,
Dhananjay

View solution in original post

Level 2 Adventurer

Re: unable to install pl/python extension

Hi dhananjayjejur,

 

Great! Thanks for your detailed explaination! Its finnally working now. I think I messed it up because I was not setting these env variables via command line. I have used the GUI for that. Maybe for the wrong user.


Regards,

localhost

Adventurer

Re: unable to install pl/python extension

On one of my machines (PG11), installing edb languages and following Dhananjay's advice (NB specific paths) worked fine.

On the other I had to use dependency walker (tip found on stackoverflow, windows) to find out that, for some reason, on PG 10.7, plpython3.dll required python 3.4 instead of python 3.7 which was installed by stack-builder (EDB languages), not offering a choice here.

If you study dependency walker output, do not follow every missing dll. Most of the errors are red herrings, just check the status of pythonxx.dll.

If it has a question mark, you won't be able to continue without more work.

Upgrading to the latest minor version is a good option, certainly in my case of a 10.7 installation (which uses python 3.4).

Version 10.11 uses Python 3.7, which agrees to the EDB languages download (at this time).

And for windows, the last .msi installer for Python 3.4 appears to be Python 3.4.4 (2015), so the choice was easy.

After upgrading to 10.11 I could simply follow (mutatis mutandis) Dhananjay's good advice.

 

EDIT:

I could not get the 10.7 (upgraded to 10.11) solution permanent. That is, after each startup of my machine, the example function pymax() would not work. SELECT pymax(1,2) fails with the message that plpython3.dll could not be found. Restart if the server with (environment) PYTHONHOME=<your pythoninstallationpath> solves it, but not permanently. The 11.6 installation works correctly every time.

I really tried everything I could think of, e.g. make the PYTHONHOME setting permanent, use a startup program when loading the service (Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postgresql-x64-11\ImagePath), setting the environment up as in the solution (PATH etc.).

I gave up on this one, maybe someone has a suggestion?

I'll try harder with plpgsql, but a stable python would have made life (a lot) easier for me at least.

Jan

EDB Team Member

Re: unable to install pl/python extension

Hi janhec,

 

Thanks for sharing your experience on python dependency for versoin PG10.7 and 11.

Would you be able to share the dependency walker  followed and the errors.

Adventurer

Re: unable to install pl/python extension


@Rahul wrote:

Hi janhec,

 

Thanks for sharing your experience on python dependency for versoin PG10.7 and 11.

Would you be able to share the dependency walker  followed and the errors.


Hi Rahul,

Start cmd.exe

Type depends.exe return

Load plpython3.dll

dependencyload.png

Have patience, this can take time when dependencies are hard to find…

dependencywindow.png

Ignore red text. Most is caused by the fact that depency walker was made a long time ago.
If desired, check google on depency walker and API. It will show several types of response to ignore.

Collapse the tree at the first node below PLPYTHON3.DLL, continue until you find PYTYHONxx.DLL, here PYTHON37.DLL.

dependencydetail.png

If PYTHONxx.DLL has a (yellow or red) question mark, it could not be located properly.

Here, it looks ok. That's because I already solved the problem.

What can go wrong? You may not have depends.exe, because it is not part of standard windows.

Or it may not be in your path.
It is part of Visual Studio, the support tools, Platform SDK etc. and can be downloaded freely.