How to install PL/Python on PostgreSQL 9.3 x64 Windows 7?

Question

I have tried to install the PL/Python v2.x language inside PostgreSQL on my database running the query:

CREATE EXTENSION plpythonu;

(I got this from http://www.postgresql.org/docs/9.3/static/plpython.html)

But I'm getting this error:

ERRO:  não pôde acessar arquivo "$libdir/plpython2": No such file or directory
********** Error **********
ERRO: não pôde acessar arquivo "$libdir/plpython2": No such file or directory
SQL state: 58P01

How to install this in an automated way? I need to install it on many computers.

Solution

Typically this error message is a misleading one emitted by the Windows API LoadLibrary call. What it actually means is closer to:

Error when loading plpython2.dll: Cannot find dependency DLL python27.dll on PATH

but instead Windows just acts like it is plpython2.dll its self that could not be loaded.

You can tell if this is the issue by checking the lib directory of your PostgreSQL install for a plpython2.dll. If it's there, but you get this error, then it's a missing Python runtime. If there's no plpython2.dll at all then your PostgreSQL install is missing plpython2, something I'm raising with the packaging team.

If you have plpython2.dll but it won't load, you need to install the Python runtime that matches the PostgreSQL version. It must be the same Python major version as was used to compile PostgreSQL, e.g. if Python 2.7 was used to compile PostgreSQL, Python 2.6 won't work to run plpython.

It'd be nice if installing the required runtime was automated via the installer, but at present it isn't. It's also not properly documented, which I'll take up with the packaging team The correct runtime to install is now documented in doc\installation-notes.html inside the install directory, which you can also get to via PostgresSQL 9.3 -> Documentation -> Installation Notes in the Start menu.

For older relases that lack this information in their "installation notes" file, if you're not sure what version of Python is required you can use depends.exe (dependency walker) to see which Python DLL it's linked to. You need the same architecture of Python too - if you're installing 64-bit PostgreSQL you need 64-bit Python, etc.

The PostgreSQL 9.3 packages require Python 27. So go download Python 2.7 from http://python.org/ (not ActiveState, they aren't necessarily compatible). Make sure Python is added to the PATH by the installer (it's an option when you run the installer). Then re-try after re-starting PostgreSQL.

You can automate installation of Python with:

start /wait msiexec /i python-2.7.3.amd64.msi /qb /passive TARGETDIR=%SystemDrive%\Python27_x64 ALLUSERS=1

where python-2.7.3.amd64.msi is the filename of the Python binary you installed, and you're installing the 64-bit version to C:\Python27_x64. Adjust as desired.

Comments

[Burhan Khalid]

" if Python 2.7 was used to compile PostgreSQL, Python 2.6 won't work to run plpython."?

[Craig Ringer]

@BurhanKhalid The have different DLL names (sonames). plpython2.dll will be linked to python27.dll and won't find python26.dll if it's present. Even if it was, Python isn't ABI compatible across major releases. Open plpython2.dll with depends.exe to see what I mean.

[Burhan Khalid]

I think you meant to say "If Python 2.7 was used to compile plpython", no?

[Craig Ringer]

@BurhanKhalid Well, sure, but plpython is a contrib that's packaged and distributed with PostgreSQL. In practice there's little difference.

[Dan Lenski]

The documentation doesn't seem to mention this anywhere, but the Windows builds of PostgreSQL 9.3 don't come with any support for plypython2u anymore. The plpython2.dll simply doesn't exist on my system. So it's necessary to upgrade to Python 3.2 to use with PG 9.3 builds unless you want to roll your own. More details here: forums.enterprisedb.com/posts/list/2878.page

[Craig Ringer]

@DanLenski That refers to PostgreSQL 9.1.0. But really, no plpython2? That doesn't seem right...

[Dan Lenski]

@Craig, I'm stumped too... But my stock amd64 MSI install of Pg9.3 is most definitely missing pgpython2.dll! Would be delighted to hear this was an accidental omission and there's a straightforward way to get it back in the standard build. Especially since the manual still says Python 2.x is the default!

[Craig Ringer]

@DanLenski MSI install? The PostgreSQL installers aren't MSIs. Where'd you download this from?

[Dan Lenski]

You're right, it's an .exe rather than a .msi; the aforementioned DLL is missing from a fresh install of 9.3.5 on Win7. get.enterprisedb.com/postgresql/…

[Craig Ringer]

@DanLenski Ugh, plpython2 appears to be missing from my install too. I'll raise it on the mailing list... done; thread at postgresql.org/message-id/[email protected]