postgres 9.5 create function plpthon3u resets connections to server

On 05/23/2016 11:18 AM, Peter Crosbie wrote:

this is a repost of a message I mistakenly sent to pgslq-novice,
although it is probably a simple question

I believe my issue may be that I don't have the correct python
interpreter on the server's path. I couldn't find
the required version in the documents and determined what I thought was
required by using dependency walker
on the plpython3.dll in postgres lib/.

A pointer to more detailed doc or example would be great. Thanks.

Anyway, here is my issue:

postgresql 9.5 on windows 10, x64.

Where did you get Postgres from and how did you install it?

I have created the extension plpython3u with python 3.3.5 on the
server's path and it appeared to create the extension successfully:

Can you show how you created the extension?

|SELECT * FROM pg_available_extensions WHERE name like '%python%' order
by name; name | default_version | installed_version | comment
-------------------+-----------------+-------------------+------------------------------------------
- hstore_plpython2u | 1.0 | | transform between hstore and plpython2u
hstore_plpython3u | 1.0 | | transform between hstore and plpython3u
hstore_plpythonu | 1.0 | | transform between hstore and plpythonu
ltree_plpython2u | 1.0 | | transform between ltree and plpython2u
ltree_plpython3u | 1.0 | | transform between ltree and plpython3u
ltree_plpythonu | 1.0 | | transform between ltree and plpythonu
plpython2u | 1.0 | | PL/Python2U untrusted procedural language
plpython3u | 1.0 | 1.0 | PL/Python3U untrusted procedural language
plpythonu | 1.0 | | PL/PythonU untrusted procedural language (9 rows) |

However when I attempt to create the following function (from the pg
docs <http://www.postgresql.org/docs/9.5/static/plpython-funcs.html&gt;)

|CREATE FUNCTION pymax (a integer, b integer) RETURNS integer AS $$ if a

b: return a return b $$ LANGUAGE plpython3u; |

the psql (or pgadmin3) terminal's connection is reset.

The python 3.3 on the path is anaconda's distb and runs fine on its own.
I couldn't find the required version of python in the postgresql docs

The required version will be what ever plpythonu was built against when
the binary was built. At a guess and given what you are seeing that is
not the version in the Anaconda distribution.

and used dependency walker as described here Postgres database crash
when installing plpython
<http://stackoverflow.com/questions/12010344/postgres-database-crash-when-installing-plpython?rq=1&gt; to
find the required dll that plpython3.dll in the server's lib/ requires.

What is the dependency walker output?

Can anyone help me with what I have missed?

This question is also posted on stackoverflow here
<https://stackoverflow.com/questions/37382511/postgres-9-5-create-function-plpthon3u-resets-connections-to-server&gt;.

Thanks in advance for your help.

Many thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian, many thanks for picking this up.

Going back to get the link for the installation download, I notice the
"readme.txt" link next to the installer. Looking at that file for the first
time, it clearly lays out how to include the language packs including
plpython. After following the instructions in the readme.txt the plpython
extension is working fine. No excuse for not reading the readme. My bad.

As you note in your response, I was not matching the required version of
python. The bottom line is that postgresql does seem to be relatively
sensitive to the particular distribution of python, not just version - (I
had matched the versions postgres python distb 3.3.4 and anaconda 3.3.4.)

Setting the server's path to use
C:\EnterpriseDB\LanguagePack\9.5\x64\Python-3.3 as described in the
readme.txt on the download site was all that it took to get it working
correctly.

Thanks again picking this up and getting me pointed in the right direction.

For completeness, here are the answers to your specific questions.

Where did you get Postgres from and how did you install it?

postgresql-9.5.3-1-windows-x64.exe from here
<http://www.enterprisedb.com/products-services-training/pgdownload#windows&gt;.

Can you show how you created the extension?

CREATE EXTENSION plpython3u;

The required version will be what ever plpythonu was built against when the
binary was built. At a guess and given what you are seeing that is not the
version in the Anaconda distribution.

Agreed and that is indeed the issue, as noted above in the fix.

What is the dependency walker output?

... snip

[ 6] PLPYTHON3.DLL
[ ? ] LIBINTL-8.DLL
[ ? ] PYTHON33.DLL
[ ? ] POSTGRES.EXE
[ 6] MSVCR120.DLL
[ ^6] KERNEL32.DLL
[F^6] NTDLL.DLL

On 05/23/2016 01:42 PM, Peter Crosbie wrote:

Adrian, many thanks for picking this up.

Going back to get the link for the installation download, I notice the
"readme.txt" link next to the installer. Looking at that file for the
first time, it clearly lays out how to include the language packs
including plpython. After following the instructions in the readme.txt
the plpython extension is working fine. No excuse for not reading the
readme. My bad.

As you note in your response, I was not matching the required version of
python. The bottom line is that postgresql does seem to be relatively
sensitive to the particular distribution of python, not just version -
(I had matched the versions postgres python distb 3.3.4 and anaconda 3.3.4.)

Yes, probably related to how the Python versions where compiled. Also,
from my limited experimentation, the Anaconda Python works best when you
use it with other programs in the Anaconda distribution.

Setting the server's path to use
C:\EnterpriseDB\LanguagePack\9.5\x64\Python-3.3 as described in the
readme.txt on the download site was all that it took to get it working
correctly.

Thanks again picking this up and getting me pointed in the right direction.

For completeness, here are the answers to your specific questions.

Thanks for this, it will help anyone else that goes down the same path.

Where did you get Postgres from and how did you install it?

postgresql-9.5.3-1-windows-x64.exe from here
<http://www.enterprisedb.com/products-services-training/pgdownload#windows&gt;.

Can you show how you created the extension?

CREATE EXTENSION plpython3u;

The required version will be what ever plpythonu was built against when
the binary was built. At a guess and given what you are seeing that is
not the version in the Anaconda distribution.

Agreed and that is indeed the issue, as noted above in the fix.

What is the dependency walker output?

... snip

[ 6] PLPYTHON3.DLL
[ ? ] LIBINTL-8.DLL
[ ? ] PYTHON33.DLL
[ ? ] POSTGRES.EXE
[ 6] MSVCR120.DLL
[ ^6] KERNEL32.DLL
[F^6] NTDLL.DLL

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general