cancel
Showing results for 
Search instead for 
Did you mean: 

Plpython3u cases server to crash

Adventurer

Plpython3u cases server to crash

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. 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 from the Python installed from the language pack into the windows/Sys32 and C:\Program Files\PostgreSQL\10\lib folders.

I can access python from the terminal without any problems. I checked the depenceys of plpython.dll and it showed python37.dll.

I’m able to create the extension. My gut is telling me it has something to do with me putting the python37.dll file into the windows/sys32 folder. However, if I don’t do that, I get an error when I try to create the extension.

Any help would be appreciated

**********************************************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
3 REPLIES 3
Highlighted
Adventurer

Re: Plpython3u cases server to crash

Finally found a post that answers the question: PYTHON_HOME should be PYTHONHOME. Also, you should not move the pyhton37.dll file into windows/sys. There is a lot of misleading information out there.
Highlighted
Adventurer

Re: Plpython3u cases server to crash

New issue, every time I restart my computer the script creates the same issue as before.  In order to solve the issue, I have to move the PYTHON37.dll file back into windows\system32 folder and once again set

 

set PYTHONHOME=C:\edb\languagepack\v1\Python-3.7
set PATH=C:\edb\languagepack\v1\Python-3.7; C:\edb\languagepack\v1\Perl-5.26\bin; C:\edb\languagepack\v1\Tcl-8.6\bin;%PATH%

 

and then reset the server with 

 

pg_ctl.exe -D ../data restart.

 

In once again starts working. 

 

I have a feeling the PYTHONHOME path is being lost but can't figure how to make it work every time. 

 

Any ideas?

Highlighted
Adventurer

Re: Plpython3u cases server to crash

Got it 

 

In windows create an enviroment system variable called PYTHONHOME and set it to the location of where python is in the language folder.

Then the system path variable add new

Add 

%PYTHONHOME%

Then add a new entry and add 3 entries

C:\edb\languagepack\v1\Python-3.7

C:\edb\languagepack\v1\Perl-5.26\bin

C:\edb\languagepack\v1\Tcl-8.6\bin

 

for a total of 4 entires.  


Close all the windows and restart the computer.  Open up the enviormental variables again and make sure they are there.