postgres and python

Asked 31/12, 2012 at 22:3 Answered 26/2, 2021 at 9:8

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?

Castalia answered 31/12, 2012 at 22:3 Comment(4)

underdark.wordpress.com/2010/12/10/… tried this? Seems to work with version 8.4 – Query 31/12, 2012 at 22:8

When I tried the first lanuage create I got this error: ERROR: function plpython_call_handler() does not exist – Castalia 31/12, 2012 at 23:44

BTW, that was using the new python 3.3 that I installed. – Castalia 2/1, 2013 at 10:27

I've written a better explanation for this here: https://mcmap.net/q/429679/-how-to-install-pl-python-on-postgresql-9-3-x64-windows-7 – Assembly 14/6, 2014 at 9:32

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://mcmap.net/q/429679/-how-to-install-pl-python-on-postgresql-9-3-x64-windows-7

Assembly answered 11/1, 2013 at 2:33 Comment(0)

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

Install python 3.2.* version only on your system.
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.)
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.
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.

Benefic answered 4/2, 2013 at 7:41 Comment(2)

Yes, this worked for me as well. The proper version is critical. – Fadiman 25/2, 2015 at 23:24

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. – Rosemarierosemary 29/5, 2017 at 15:14

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://mcmap.net/q/429679/-how-to-install-pl-python-on-postgresql-9-3-x64-windows-7

Assembly answered 11/1, 2013 at 2:33 Comment(0)

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.

Mannered answered 31/12, 2012 at 22:16 Comment(14)

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 – Castalia 31/12, 2012 at 23:17

Sorry, that was the wrong dir. I did find in the lib dir this file: plpython3.dll – Castalia 1/1, 2013 at 0:8

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 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. – Assembly 2/1, 2013 at 0:4

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! – Castalia 2/1, 2013 at 11:8

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 https://mcmap.net/q/12893/-adding-python-to-path-on-windows . This means you must have Python 3.2, not 3.1, not 3.3, ... – Assembly 2/1, 2013 at 12:9

@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 – Castalia 6/1, 2013 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. – Assembly 7/1, 2013 at 1: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.... – Castalia 7/1, 2013 at 2: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 – Castalia 7/1, 2013 at 14:18

@Castalia CREATE LANGUAGE plpython3u for Python3, or install the required version of Python2. – Assembly 8/1, 2013 at 1: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? – Castalia 11/1, 2013 at 1: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. – Castalia 11/1, 2013 at 2:15

@Castalia 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. – Assembly 11/1, 2013 at 2: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. – Rosemarierosemary 29/5, 2017 at 15:33

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

Somnambulation answered 2/6, 2014 at 18:48 Comment(0)

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;

Ignacioignacius answered 26/2, 2021 at 9:8 Comment(3)

But what will be CenOS equivalent command for this ? since CentOS doesn't have apt. – Tod 3/8, 2021 at 11:0

@AmeerUlIslam i've never used centOS, but here is link to your RPM repo: centos.pkgs.org/7/postgresql-10-x86_64/… – Ignacioignacius 3/8, 2021 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 – Tod 4/8, 2021 at 5:14

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags