22

In postgres 9.2 I am trying to create a python program that can be a trigger. I want to run an external program (an exe on the local disk) so I am using python to run it. When I try to create a simple program like this:

CREATE FUNCTION one ()
RETURNS int
AS $$
# PL/Python function body
$$ LANGUAGE plpythonu;

I get the error:

ERROR:  language "plpythonu" does not exist
HINT:  Use CREATE LANGUAGE to load the language into the database.

When I run: 

CREATE LANGUAGE plpythonu

I get the error:

ERROR:  could not access file "$libdir/plpython2": No such file or directory

I am using Windows 7 and python 2.5 .

I have looked in many places but cannot find a solution.

Any ideas?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Jim
  • 1,056
  • 1
  • 13
  • 19
  • http://underdark.wordpress.com/2010/12/10/plpython-pythonic-trigger-functions-for-postgres/ tried this? Seems to work with version 8.4 – XORcist Dec 31 '12 at 22:08
  • When I tried the first lanuage create I got this error: ERROR: function plpython_call_handler() does not exist – Jim Dec 31 '12 at 23:44
  • BTW, that was using the new python 3.3 that I installed. – Jim Jan 02 '13 at 10:27
  • 1
    I've written a better explanation for this here: http://stackoverflow.com/a/24218449/398670 – Craig Ringer Jun 14 '14 at 09:32

5 Answers5

10

I have just solved this problem, literally a few days back. The solution is quite involved. Here it goes.

  1. Install python 3.2.* version only on your system.

  2. In Postgresql use the 'CREATE LANGUAGE plpython3u' command to install Python 3 language support. More often than not, it will give the following error "unable to load ".....\plpython3.dll" error 126. (Note if it installs correctly, no error will be displayed.)

  3. In case you get the above error, goto your python installation directory (default is C:\python32) and look for "python3.dll" in the DLL's folder. Copy this file to your Postgresql 'lib' folder in the installation directory of Postgres (default is c:\program files\postgres\9.x\lib\"). Rename this copied file to python32.dll.

  4. Now run the 'CREATE LANGUAGE plpython3u' command again. It should work this time.

To verify, check out the pg_available_extensions view in the system tables of postgresql. The row containing plpython3u should have a version number in the 'installed version' column.

Note : This only works for plpython3u language. I do not know any similar process for plpython2u.

Raghavendra Kumar
  • 708
  • 8
  • 23
  • 1
    Yes, this worked for me as well. The proper version is critical. – user1062589 Feb 25 '15 at 23:24
  • 1
    For Postgresql 9.6, which uses Python 3.3 it is essential to install python 3.3 from www.python.org. For Windows, make sure that the python33 path is added to the PATH environment variable. Read the next entry by jlujan for more details. I used Dependency Walker to figure out the details. – marcopolo May 29 '17 at 15:14
8

To resolve this for plpython3, it was necessary to:

  • Install Python 3.2
  • Run CREATE LANGUAGE plpython3u

Update: I've written a much better explanation here: https://stackoverflow.com/a/24218449/398670

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
5

Postgres uses the ActiveState distros of Python. Most likely, your 2.5 is too outdated and Postgres cant load the plpython dll or didn't install it because there was no suitable python. I think recent postgres is buil6 against Python3 not 2.x. You can look in the postgres lib directory for plpython3.dll to find out what you need.

jlujan
  • 1,188
  • 7
  • 10
  • I uninstalled 2.5 and installed 3.3 and I get the same errors. I do not see any plpython files in that dir. The dir is C:\Program Files (x86)\PostgreSQL\9.2\bin – Jim Dec 31 '12 at 23:17
  • Sorry, that was the wrong dir. I did find in the lib dir this file: plpython3.dll – Jim Jan 01 '13 at 00:08
  • 1
    Windows produces an error like `plpython.dll: No such file or directory` when it cannot load a *dependency* of `plpython.dll`. This error is confusing, but there isn't much we can do about it. I suggest opening `plpython.dll` with Dependency Walker from http://www.dependencywalker.com/ and verifying that the correct Python DLL is actually found. If it isn't, you might need to add the correct Python version to your system PATH. – Craig Ringer Jan 02 '13 at 00:04
  • That wwas helpful! I received some errors when I tried it on the plpython3.dll file. It said it could not find the following files: LIBINTL.DLL, POSTGRES.EXE, PYTHON32.DLL. I am not sure how to fix this. Can I get a new plpython3.dll? Should I reinstall all of ppostgres? If I do will I lose the databases? In the past when I closed postgres I just hit the Windows close button... Do you think that caused the problem? Thanks, @Craig! – Jim Jan 02 '13 at 11:08
  • 1
    `libintl.dll` and `postgres.exe` are in the PostgreSQL `bin` directory, so they'll be found just fine when PostgreSQL loads the module. The directory containing `PYTHON32.DLL` must be on the `PATH`. See http://stackoverflow.com/a/6318188/398670 . This means you must have *Python 3.2*, not 3.1, not 3.3, ... – Craig Ringer Jan 02 '13 at 12:09
  • @Craig, I uninstalled python 3.3 and installed 3.2.3. I put the path to python3.dll in the path (C:\Python32\DLLs). I also found PYTHON32.DLL in C:\Windows\SysWOW64 and I added that to the path. None of tit worked... When I run create language plpythonu I still get the error: ERROR: could not access file "$libdir/plpython2": No such file or directory ********** Error ********** ERROR: could not access file "$libdir/plpython2": No such file or directory SQL state: 58P01 – Jim Jan 06 '13 at 17:58
  • Don't add SysWOW64 to the PATH. The DLL should be in the Python install directory, and that's what you should add. – Craig Ringer Jan 07 '13 at 01:21
  • I should have said that I added the python3.dll to the path AND THEN TRIED THE CREATE AND GOT THE ERROR SO I added the second path and then got the error again.... – Jim Jan 07 '13 at 02:19
  • @Craig, Justo be clear: I added C:\Python32 to the path and I restarted the postgres server and still got the following error when I tried to do the CREATE LANGUAGE plpythonu. The error is: ERROR: could not access file "$libdir/plpython2": No such file or directory ********** Error ********** ERROR: could not access file "$libdir/plpython2": No such file or directory SQL state: 58P01 – Jim Jan 07 '13 at 14:18
  • @Jim `CREATE LANGUAGE plpython3u` for Python3, or install the required version of Python2. – Craig Ringer Jan 08 '13 at 01:39
  • @Craig,Thanks!! That command allowed me to run a python program. The python that I am trying to run needs to run a program on the C: drive of my machine. I have an empty BAT file there. When I try to run it from I get an error. It is always the same error. ERROR: WindowsError: [Error 5] Access is denied. I tried using a subprocess.call and then I tried os.startfile. I think that since the BAT file is empty it should not have a problem with it. As a little test I wrote to a file on the C drive and it worked. If I can write to a file I should be able to run a file, right? Any ideas? – Jim Jan 11 '13 at 01:46
  • @Craig, a few minutes after I sent that I determined that I needed extra double quotes around the command. I no longer have that error but the program that I am trying to run does not come up. It is a Windows program and I would expect the app to come up. I am using os.system. Any ideas? Thanks. – Jim Jan 11 '13 at 02:15
  • @Jim Post a new question for a new issue. Link to it here if you like. Please take the time to make it detailed and comprehensive - show the code, the versions, error output, etc. – Craig Ringer Jan 11 '13 at 02:34
  • Once you get CREATE EXTENSION plpython3u to work ok, do not forget to change the last line of a pyhon function from $$ LANGUAGE plpythonu to $$ LANGUAGE plpython3u. – marcopolo May 29 '17 at 15:33
2

I'm installed python 3.2(x64) y postgres 9.3 (x64)

Execute this code CREATE EXTENSION plpython3u

Do not forget to check the "installation-note.html" your version of postgresSQL there are specific python version for your postgresSQL

Zong
  • 6,160
  • 5
  • 32
  • 46
user3700545
  • 21
  • 1
1

sorry for mining, but for all of those, who are looking for missing packet (in my case on Ubuntu 18.04):

sudo apt-get install -y postgresql-plpython3-10

and then:

create extension plpython3u;

Krzysztof Mochocki
  • 188
  • 2
  • 10
  • But what will be CenOS equivalent command for this ? since CentOS doesn't have apt. – Ameer Ul Islam Aug 03 '21 at 11:00
  • 1
    @AmeerUlIslam i've never used centOS, but here is link to your RPM repo: https://centos.pkgs.org/7/postgresql-10-x86_64/postgresql10-plpython3-10.11-2PGDG.rhel7.x86_64.rpm.html – Krzysztof Mochocki Aug 03 '21 at 18:28
  • it worked thanks. here is the code -> sudo yum install postgresql12-plpython3 (I'm using posgresql 12) . the repo it's pulling this from is pgdg12 – Ameer Ul Islam Aug 04 '21 at 05:14