cancel
Showing results for 
Search instead for 
Did you mean: 

unable to install pl/python extension

SOLVED
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

9 REPLIES 9
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

Highlighted
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

Level 2 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.

Level 2 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.

Adventurer

Re: unable to install pl/python extension

Hello all,


I have spent the better part of the day trying to get Python scripting to work.  I'm trying to convince my boss to use PostgreSQL because of its python functionality.   However, I get the same issue as OP.  The server crashes when I create the function using langauge plpython3u.

 

On a fresh install of windows 10, I installed PostgreSQL 10.11.  I installed the language pack.  I copied the pyton37.dll into the windows/Sys32 and C:\Program Files\PostgreSQL\10\lib folders.  

 

I followed the steps outlined in this post.  The only thing that I notice  different is 3 functions are missing when I run \dx_ plpython3u.  I can access python from the terminal without any problems.  I checked the depenceys of plpython.dll and it showed python37.dll. 

 

**********************************************steps *************************************

c:\Program Files\PostgreSQL\10\bin>set PYTHON_HOME=C:\edb\languagepack\v1\Python-3.7
c:\Program Files\PostgreSQL\10\bin>set PATH=C:\edb\languagepack\v1\Perl-5.26\bin;C:\edb\languagepack\v1\Python-3.7;C:\edb\languagepack\v1\Tcl-8.6\bin;%PATH%
c:\Program Files\PostgreSQL\10\bin>cd "c:\Program Files\PostgreSQL\10\bin"
c:\Program Files\PostgreSQL\10\bin>pg_ctl.exe -D ..\data restart
waiting for server to shut down.... done
server stopped
waiting for server to start..........................2020-01-30 23:13:48.223 PST [15296] LOG:  listening on IPv6 address "::", port 5432
2020-01-30 23:13:48.252 PST [15296] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-01-30 23:13:48.275 PST [15296] LOG:  redirecting log output to logging collector process
2020-01-30 23:13:48.275 PST [15296] HINT:  Future log output will appear in directory "log".
 done
server started
c:\Program Files\PostgreSQL\10\bin>psql.exe -U postgres
Password for user postgres:
psql (10.11)
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 10.11, compiled by Visual C++ build 1800, 64-bit
(1 row)

postgres=# \dx+ plpython3u
Objects in extension "plpython3u"
 Object description
---------------------
 language plpython3u
(1 row)

postgres=# CREATE OR REPLACE FUNCTION sp1() RETURNS integer AS
postgres-# $$
postgres$#     return 1
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.
******************************************log***********************************
2020-01-30 23:13:48.312 PST [18204] LOG:  database system was shut down at 2020-01-30 23:13:25 PST
2020-01-30 23:13:48.340 PST [15296] LOG:  database system is ready to accept connections
Fatal Python error: initfsencoding: unable to load the file system codec
ModuleNotFoundError: No module named 'encodings'
 
Current thread 0x00000d88 (most recent call first):
2020-01-30 23:19:52.625 PST [15296] LOG:  server process (PID 3472) was terminated by exception 0xC0000409
2020-01-30 23:19:52.625 PST [15296] DETAIL:  Failed process was running: CREATE OR REPLACE FUNCTION sp1() RETURNS integer AS
 $$
     return 1
 $$ LANGUAGE plpython3u;
2020-01-30 23:19:52.625 PST [15296] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2020-01-30 23:19:52.625 PST [15296] LOG:  terminating any other active server processes
2020-01-30 23:19:52.626 PST [10384] WARNING:  terminating connection because of crash of another server process
2020-01-30 23:19:52.626 PST [10384] 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.
2020-01-30 23:19:52.626 PST [10384] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-01-30 23:19:52.627 PST [11520] WARNING:  terminating connection because of crash of another server process
2020-01-30 23:19:52.627 PST [11520] 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.
2020-01-30 23:19:52.627 PST [11520] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-01-30 23:19:52.888 PST [14696] FATAL:  the database system is in recovery mode
2020-01-30 23:19:52.892 PST [15296] LOG:  all server processes terminated; reinitializing
2020-01-30 23:19:52.925 PST [9004] LOG:  database system was interrupted; last known up at 2020-01-30 23:18:48 PST
2020-01-30 23:19:53.203 PST [12468] FATAL:  the database system is in recovery mode
2020-01-30 23:19:53.446 PST [9004] LOG:  database system was not properly shut down; automatic recovery in progress
2020-01-30 23:19:53.450 PST [9004] LOG:  redo starts at 0/1677938
2020-01-30 23:19:53.452 PST [9004] LOG:  invalid record length at 0/167FF30: wanted 24, got 0
2020-01-30 23:19:53.452 PST [9004] LOG:  redo done at 0/167FEF8
2020-01-30 23:19:53.508 PST [15296] LOG:  database system is ready to accept connections
****************************************************************************
any help would be appreciated.

 

Level 2 Adventurer

Re: unable to install pl/python extension

I thought I had this under control. Then I tried to get it running for a friend, and failed.

I've built up lots of text about what did and did not happen, but I will just focus on a single item:

the registry.

I expect HKEY_LOCAL_MACHINE\SOFTWARE\Python to have several subkeys, 1 for every python version installed.

If you do not see this, consider installing python37 through the usual channel, https://www.python.org/downloads .

Remove or replace the earlier python37.dll in <windows>system32 and the lib folder of postresql with the dll indicated by HKEY_LOCAL_MACHINE\SOFTWARE\Python\PythonCore\3.7\PythonPath.

Try again, in case of failure, try using the path variant with the python folder up front, and/or envvars PYTHON_HOME - PYTHONPATH.

 

Btw: restarting with pg_ctl has not worked well for me. Often the service (postgresql-x64-11) will not be running after restart. I reboot to make sure I have a properly working server without surprises (especially after rebooting).

 

My reason for saying this is that I could induce succes and failure by changing the folder name as in C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python37_64, which I found in my registry key. When I added an _ to the foldername, the folder could obviously not be found through the registry and I got a fail, when putting back the original value, it worked again. So I'm pretty sure this registry key has to be right and you need the dll's from the corresponding folder. It may be that stackbuilding the language pack does not set it, but I did not check this. Stackbuilder certainly did not overwrite these values for the better or the worse.

 

Hope this helps another bit, please tell us.

 

Errorcode 0xC0000409 refers to ntstatus.h, which belongs to the windows kernel driver SDK, see https://docs.microsoft.com/en-us/windows-hardware/drivers/kernel/using-ntstatus-values, but quicker, just google, avoid MS and look for stackoverflow: https://stackoverflow.com/questions/23409809/windows-7-exception-code-0xc0000409, the first answer is quite useful for further investigation of this STATUS_STACK_BUFFER_OVERRUN, which I am not going to do, but people in EDB might. You will want to prevent such errors within a windows driver, it looks like there might be a(n infinite) loop which causes this overflow, perhaps by not releasing a windows handle in time. The stackoverflow answer provides tools for investigation.