Has anyone gotten plperl to work with Postgres 9.1 on Windows?

Question

I've been unable to get plperl to work with Postgres 9.1 on Windows.

The same problem is described here, but with no solution so far: http://postgresql.1045698.n5.nabble.com/BUG-6204-Using-plperl-functions-generate-crash-td4802111.html

REPRO

Install Perl 5.14 32-bit for Windows from here: http://downloads.activestate.com/ActivePerl/releases/5.14.2.1402/ActivePerl-5.14.2.1402-MSWin32-x86-295342.msi

Add Perl to the system path variable Install Postgres 9.1.2 32-bit for Windows from Enterprise DB www.enterprisedb.com/products/pgdownload.do#windows

Now create a test DB, add perl to it, and try to create a function:

postgres=# create database plperl_test;
CREATE DATABASE
postgres=# \c plperl_test
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
You are now connected to database "plperl_test" as user "postgres".
plperl_test=# create language plperl;
CREATE LANGUAGE
plperl_test=# create function perl_test() returns void as
plperl_test-# $$
plperl_test$# $$
plperl_test-# language plperl;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Note that this caused the Postgres server process to shut down. Yikes!

When I look in my Postgres log file, I find this...

2011-12-29 15:51:08 PST STATEMENT:  create function perl_test() returns void as
    $$
    $$
    language plperl;
2011-12-29 15:51:26 PST LOG:  server process (PID 10364) was terminated by exception 0xC0000005
2011-12-29 15:51:26 PST HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2011-12-29 15:51:26 PST LOG:  terminating any other active server processes
2011-12-29 15:51:26 PST WARNING:  terminating connection because of crash of another server process
2011-12-29 15:51:26 PST DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2011-12-29 15:51:26 PST HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2011-12-29 15:51:26 PST LOG:  all server processes terminated; reinitializing
2011-12-29 15:51:36 PST FATAL:  pre-existing shared memory block is still in use
2011-12-29 15:51:36 PST HINT:  Check if there are any old server processes still running, and terminate them.

Answer 1

I was surprised to find no good answers from Google on this. It seems that Activstate works for some but not others. There were suspicions about conflicts with different versions of windows system libraries, but nothing definitive. However, I was able to finally build a plperl.dll that does work, using strawberry perl.

Here is step-by-painful step instructions to get plperl working with strawberry perl. The trick is to recompile postgresql and plperl, but install plperl over your existing postgresql install.

first, you will need the following:

1. mingw ( http://www.mingw.org )
2. strawberry perl. http://www.strawberryperl.org
3. Postgresql sources. Make sure you get the version that matches your install.

The mingw site is confusing as hell. There is no single package to download. You have to download an installer that downloads everything else. Here's a quick link to it.

http://sourceforge.net/projects/mingw/files/Installer/

In their installer, select both C and C++, and the MSYS environment to install. Mingw won't pollute your environment, so install it in any old location.

As for perl, I don't think activestate comes with the files (shared libraries) necessary to do this, and I've found strawberry's CPAN support to be superior (vs activestate's proprietary ppm sites) and activestate's douchebag move of putting old versions behind a paywall (and forcing other sites to takedown their hosted copies). F.U. ActiveState.

Postgresql 9.1 was originally built against perl 5.14, so it's probably best to stick with that version.

Unpack and install all that stuff. Mingw will give you a shell (look for the shortcut in the startup menu) to compile in. Start up a shell and do not close it.

run the following to install/remove packages:

mingw-get install libminizip
mingw-get remove mingw-perl

you need to remove mingw's perl so that the build process uses strawberry perl instead. You could screw around with the path variable instead, but this way is easier

now, you need to get to the directory that you unpacked the unpacked the postgresql sources to. In the mingw shell, use the root path /c to refer to the C: drive.

Now just configure and make

./configure --with-perl
make

Note that postgres docs say to use gmake, but on mingw, it's make.

The build will probably fail while building the actual plperl.dll file. That's because the build environment doesn't produce the right command to build the dll. If you get an error like this:

dllwrap -o plperl.dll --dllname plperl.dll  --def libplperldll.def plperl.o SPI.o Util.o -L../../../src/port -Wl,--allow-multiple-definition  -Wl,--as-needed -LC:/strawberry/perl/lib/CORE -l -L../../../src/backend -lpostgres
c:/mingw/bin/../lib/gcc/mingw32/4.7.0/../../../../mingw32/bin/ld.exe: cannot find -l-L../../../src/backend
c:/mingw/bin/../lib/gcc/mingw32/4.7.0/../../../../mingw32/bin/ld.exe: cannot find -lpostgres

cd to src/pl/plperl and execute the following command:

dllwrap -o plperl.dll --dllname plperl.dll  --def libplperldll.def plperl.o SPI.o Util.o -L../../../src/port -Wl,--allow-multiple-definition  -Wl,--as-needed -L/c/strawberry/perl/lib/CORE  -L../../../src/backend -lpostgres -lperl514

(note that I have perl installed in c:\strawberry and mingw installed in c:\mingw )

At this point, you're done. you have a plperl.dll in that directory that can replace the one that comes with postgresql.

Answer 2

After playing around with a fresh installation of PostgreSQL 9.1.11 (x64) I've found ActivePerl 5.14.1 (x64) works. The key is to ensure that PostgreSQL can find the required perl DLL's in its environment PATH.

Run Dependency Walker on plperl.dll inside the postgres lib directory. In the case of PostgreSQL 9.1.11 it has a dependency on perl514.dll - so change your environment PATH to include the directory where this is located (i.e. c:\perl\bin). Recheck the DLL is detected properly by re-running Dependency Walker, then fully stop/start the PostgreSQL service you should be good to go.

If you find that the plperl.dll has a dependency on a different perl DLL then you need to use a version of ActivePerl that matches (i.e. perl58.dll would match ActivePerl 5.8, perl510.dll would match ActivePerl 5.10 - ensuring that the binary format of Perl and PostgreSQL always match (32/64bit)).

Answer 3

It says here, that (ActiveState) Perl 5.14 is required to get it working. I'm not sure about the brand as I had to use a legacy version of strawberry perl for postgres 9.0, which worked just fine. The important thing is to use the correct Perl version 5.14 for Postgres 9.1. Try to use a binary format (32 vs. 64 bit) matching the one of your postgres installation.