Postgres database crash when installing plpython

Question

I'm trying to install plpython in my Postgres 9.1 but it crashes the server:

postgres@dataserver1:~> /opt/postgres/9.1/bin/psql -d mydb
psql.bin (9.1.4)
Type "help" for help.

mydb=# create language 'plpythonu';
The connection to the server was lost. Attempting reset: Failed.

I have python 2.6.8 installed and the handler is correcty declared in the system:

select tmplname, tmplhandler, tmpllibrary from pg_pltemplate where tmplname like 'plpython%'

"plpythonu"  | "plpython_call_handler"  | "$libdir/plpython2"
"plpython2u" | "plpython2_call_handler" | "$libdir/plpython2"

And the handler is installed in the $libdir:

postgres@dataserver1:~> ll /opt/postgres/9.1/lib/postgresql/plpython*
-rwxr-xr-x 1 root root 6686333 Aug 17 14:27 /opt/postgres/9.1/lib/postgresql/plpython2.so

Any clues on this will be apreciated

EDIT

I've tried create extension plpythonu, plpython2u, and plpython3u and all of them crashes the server.

Reading a little in the docs I found the \dx command to list the installed extensions:

mydb=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

So I guess the only installed extension is plpgsql.

mydb=# select name, installed_version from pg_available_extensions where name like '%python%';
    name    | installed_version
------------+-------------------
 plpython2u |
 plpython3u |
 plpythonu  |
(3 rows)

and my extension directory:

postgres@dataserver1:/opt/postgres/9.1/share/postgresql/extension> ll *python*
-rw-r--r-- 1 root root 351 Aug 20 17:32 plpython2u--1.0.sql
-rw-r--r-- 1 root root 196 Aug 20 17:32 plpython2u.control
-rw-r--r-- 1 root root 402 Aug 20 17:32 plpython2u--unpackaged--1.0.sql
-rw-r--r-- 1 root root 351 Jun  1 02:54 plpython3u--1.0.sql
-rw-r--r-- 1 root root 196 Jun  1 02:54 plpython3u.control
-rw-r--r-- 1 root root 402 Jun  1 02:54 plpython3u--unpackaged--1.0.sql
-rw-r--r-- 1 root root 347 Aug 20 17:32 plpythonu--1.0.sql
-rw-r--r-- 1 root root 194 Aug 20 17:32 plpythonu.control
-rw-r--r-- 1 root root 393 Aug 20 17:32 plpythonu--unpackaged--1.0.sql

EDIT

I'm running on Linux SuSE, but got your point.

In my postgres instalation, the pl-lang libraries are in .../postgres/9.1/lib/postgres. Inside it there are plpython2.so and plpython3.so

Checking the dynamic linked libraries of these files:

alfonso@dataserver1:/opt/postgres/9.1/lib/postgresql> sudo ldd plpython2.so
        linux-vdso.so.1 =>  (0x00007fff5e945000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f64064df000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f64062da000)
        libutil.so.1 => /lib64/libutil.so.1 (0x00007f64060d7000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f6405e5e000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f6405ae9000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f6406b2e000)

There is no reference to any python library, neither for plpython3.so

But I have installed python 2.6 and python 3.2. Note that I wish to install the extension for python 2.

And yes, CREATE EXTENSION is still crashing:

mydb=# create extension plpython2u;
The connection to the server was lost. Attempting reset: Failed.
!> \q

Answer 1

Note: As of PostgreSQL 9.1, most procedural languages have been made into "extensions", and should therefore be installed with CREATE EXTENSION not CREATE LANGUAGE. Direct use of CREATE LANGUAGE should now be confined to extension installation scripts. If you have a "bare" language in your database, perhaps as a result of an upgrade, you can convert it to an extension using CREATE EXTENSION langname FROM unpackaged.

To install PL/Python in a particular database:

from the shell command line use createlang plpythonu dbname

Caution

createlang is deprecated and may be removed in a future PostgreSQL release. Direct use of the CREATE EXTENSION command is recommended instead.

http://www.postgresql.org/docs/9.1/static/plpython.html

EDIT

Step by step

Download the postgresql-9.1.5-1-windows.exe and install it.

First let's see what's already there.

mydb=# SELECT * FROM pg_available_extensions
mydb-# WHERE name LIKE '%python%' ORDER BY name;
    name    | default_version | installed_version |                  comment

------------+-----------------+-------------------+-------------------------------------------
 plpython2u | 1.0             |                   | PL/Python2U untrusted procedural language
 plpython3u | 1.0             |                   | PL/Python3U untrusted procedural language
 plpythonu  | 1.0             |                   | PL/PythonU untrusted procedural language
(3 Zeilen)

Nothing installed !!

Looking at the lib folder and there is only.

....\PostgreSQL\9.1\lib\plpython3.dll.

So I must try to install plpython3u.

mydb=# CREATE EXTENSION plpython3u;
ERROR:  unknown error »$libdir/plpython3.dll«

There must be something wrong with plpython3.dll.

Looking into plpython3.dll there is a reference to python32.dll.

......

Export Table:
  Name:                          plpython3.dll
  Time Date Stamp:               0x502B366A (15.08.2012 06:40:58)
  Version:                       0.00
  Ordinal Base:                  1
  Number of Functions:           9
  Number of Names:               9

  Ordinal   Entry Point   Name
        1   0x00001005    Pg_magic_func
        2   0x00001019    PyInit_plpy
        3   0x0000101E    _PG_init
        4   0x00001023    pg_finfo_plpython3_call_handler
        5   0x0000100F    pg_finfo_plpython3_inline_handler
        6   0x0000100A    pg_finfo_plpython3_validator
        7   0x00001028    plpython3_call_handler
        8   0x00001014    plpython3_inline_handler
        9   0x0000102D    plpython3_validator

Import Table:
  libintl-8.dll
    Import Adress Table:                0x00016628
    Import Name Table:                  0x0001617C
    Time Date Stamp:                    0x00000000
    Index of first forwarder reference: 0x00000000

    0x000169E4        28   libintl_dngettext
    0x000169D0        27   libintl_dgettext

  **python32.dll**
    Import Adress Table:                0x0001686C
    Import Name Table:                  0x000163C0
    Time Date Stamp:                    0x00000000
    Index of first forwarder reference: 0x00000000
  ....

• I download and install python-3.2.3.msi. I found only python3.dll
• Copy the python3.dll to ....\PostgreSQL\9.1\lib and rename it to python32.dll

Back in SQL Shell i typed

mydb=# CREATE EXTENSION plpython3u FROM unpackaged;

I got the message

CREATE EXTENSION

Looking for the result with:

mydb=# SELECT * FROM pg_available_extensions
mydb-# WHERE name LIKE '%python%' ORDER BY name;

There it is the installed_version 1.0 of plpython3u

and i also can see it in PgAdmin III

Hope that helps !

EDIT:

Try it manually

Step 1

mydb=# CREATE PROCEDURAL LANGUAGE plpython2u;

Step 2

mydb=# COMMENT ON PROCEDURAL LANGUAGE plpython2u IS 'PL/Python2U untrusted procedural language';

Step 3

mydb=# CREATE EXTENSION plpython2u FROM unpackaged;

Tested with Python 2.6.5