As mentioned in our previous article Building on MingW deploying on VC we often build on MingW and deploy on Windows servers running EDB distributed VC PostgreSQL builds for extensions we want that don't come packaged. One of the new ones we are really excited about is the PL/V8 and PL/Coffee ones. Could we do it and would it actually work on a VC build. YES WE CAN and yes it does. I HAZ Coffee and a V8: .

Here are some instructions we hope others will find useful. Even if you aren't on Windows, you might still find them useful since MingW behaves much like other Unix environments.

If you are on windows, and just want to start using PLV8 and PLCoffee. We have binary builds for both PostgreSQL 9.2 Windows 32-bit (pg92plv8jsbin_w32.zip) and PostgreSQL 9.2 Windows 64-bit (pg92plv8jsbin_w64.zip) which you should be able to just extract into your PostgreSQL 9.2 beta windows install. We quickly tested with EDB VC++ builds and they seem to work fine on standard VC++ PostgreSQL 9.2beta2 installs. We haven't bothered building for lower PostgreSQL, but if there is some interest, we'd be happy to try.

Building on MingW64 chain

1. Download a binary of MingW64 if you don't have one already. I think you'll need one that has at least gcc 4.5. We are using a package that has gcc 4.5.4. Details of how we setup our environment are PostGIS 2.0 Mingw64 build instructions
2. download Python27 (32-bit version we think you need 32-bit since scones seems to be only a 32-bit package its just a builder so will work fine for building your 64-bit install) (2.7.3) for windows from: http://www.python.org/download/windows/ and install. We usually install in C:\Python27 which is the default. The recommended is to use GYP instead of scons, but we couldn't get GYP to work. I think there is better support for GYP if building with VC++.
3. Download scons installer (scons-2.1.0.win32.exe) from www.scons.org and install (in your Python27)
4. Here is our build script for building the V8 engine

   OS_BUILD=64
   export PATH="/c/Python27:/c/Python27/Scripts:.:/bin:/include:/mingw/bin:/mingw/include:/c/Windows/system32:/c/Windows:/usr/local/bin:/c/ming${OS_BUILD}/svn"
   export PROJECTS=/c/ming${OS_BUILD}/projects
   export V8_REL=trunk
   cd ${PROJECTS}/v8
   #you can manually just use subversion here
   svn checkout http://v8.googlecode.com/svn/${V8_REL}/ ${V8_REL}
   cd ${PROJECTS}/v8/${V8_REL}
   #if you are building for 32-bit mingw64-w32 change arch=x64 to arch=ia32
   #note that newer versions of v8 require you to add the 
   # I_know_I_shoud_build_with_GYP whereas older versions do not
   scons.py mode=release arch=x64 toolchain=gcc importenv=PATH library=shared I_know_I_should_build_with_GYP=yes
   
   #this builds the interactive shell console you can use for debugging
   #when building for plv8 
   #you will get a warning if d8.exe is not found
   #but doesn't seem to be needed for install
   scons.py d8 mode=release arch=x64 toolchain=gcc importenv=PATH library=shared
   #for release this will reduce size of dll
   strip *.dll
   

5. When you are done running a build script that looks something like above you should see two dlls in trunk folder (v8.dll, v8preparser.dll) and an executable d8.exe
6. We really didn't want to pollute our mingw install, but these steps were required for the libraries and d8.exe to be found: Copy the v8.dll, v8prepare.dll to your mingw64/lib folder (or mingw32/lib)
7. Copy v8.dll, v8prepare.dll, d8.exe to your mingw64/bin (or mingw32) folder
8. Download the plv8 source code from git. The stale download files http://code.google.com/p/plv8js were no good and caused all sorts of frustrating Yield errors. I've asked Andrew Dunstan to update the zips so people who don't have Git can function. We used msysgit and TortoiseGit for downloading source.
9. Open the Makefile and edit path of v8 code at top
10. To get around error: undefined reference to `__cxa_throw' and also failing in production when throwing an exception, had to compile lstdc++ statically into the dll by doing this:

    we also had to make this change
    To fix change
    Change line:
    SHLIB_LINK := $(SHLIB_LINK) -lv8
    
    To: #this was our original attempt 
    # but installchecks that threw js_error would crash the postgres back end
    SHLIB_LINK := $(SHLIB_LINK) -lv8 -lstdc++
    
    #change CUSTOM_CC from g++ to gcc
    CUSTOM_CC = gcc
    
    #so we statically compiled in with this:
    SHLIB_LINK := $(SHLIB_LINK) -lv8 -Wl,-Bstatic -lstdc++ -Wl,-Bdynamic -lm
    
    #make sure this uses CUSTOM_CC instead of g++
    %.o : %.cc
    	${CUSTOM_CC} $(CCFLAGS) $(CPPFLAGS) -I $(V8DIR)/include -fPIC -c -o $@ $<

11. Our shell script for compiling against PostgreSQL 9.2beta2 compiled mingw looks like

    export PROJECTS=/c/ming64/projects
    export PG_VER=9.2beta2
    export OS_BUILD=64
    export PGPORT=8442
    export PGUSER=postgres
    export PATH=".:/usr/local/bin:/mingw/bin:/bin:/c/Windows/system32:/c/Windows"
    export PATH="${PROJECTS}/pgx64/pg${PG_VER}w${OS_BUILD}/bin:${PROJECTS}/pgx64/pg${PG_VER}w${OS_BUILD}/lib:$PATH" 
    
    cd ${PROJECTS}/postgresql/extensions/plv8js
    make clean
    USE_PGXS=1
    make clean
    make ENABLE_COFFEE=1 install
    #for release this will reduce size of dll
    strip *.dll
    #make sure all tests pass
    #You will need to have your PostgreSQL running
    # on PGPORT specified
    make installcheck  
    

12. To deploy on PostgreSQL VC++ builds you'll need to copy to PostgreSQL bin

    bin: v8.dll, v8preparser.dll
         located in your mingw64/32 bin folder: libgcc_s_sjlj-1.dll, libstdc++-6.dll
    lib: plv8.dll
    share/extension -- the plv8 and plcoffee files from your mingw64 pg92 install (for some reason the install refused to install plcoffee.sql and plcoffee.control files
    files, so we had to manually copy those from the plv8 folder

Install and Test

In any 9.2 database:

CREATE EXTENSION plv8js;
CREATE EXTENSION plcoffee;

Coffee Test adapted from: http://umitanuki.hatenablog.com/entry/2012/05/11/025816

CREATE OR REPLACE FUNCTION fibonacci(n integer)
 RETURNS integer  LANGUAGE plcoffee IMMUTABLE STRICT
AS $function$
fibonacci = (x)->
  return 0 if x == 0
  return 1 if x == 1
  return fibonacci(x-1) + fibonacci(x-2)
return fibonacci n
$function$;

SELECT i, fibonacci(i) As fib
FROM generate_series(5,30,3) As i;

i  |  fib
---+--------
 5 |      5
 8 |     21
11 |     89
14 |    377
17 |   1597
20 |   6765
23 |  28657
26 | 121393
29 | 514229

PLV8JS test from docs

CREATE FUNCTION to_jsontext(keys text[], vals text[]) RETURNS text AS
$$
        var o = {};
        for (var i = 0; i < keys.length; i++)
                o[keys[i]] = vals[i];
        return JSON.stringify(o);
$$
LANGUAGE plv8 IMMUTABLE STRICT;

SELECT to_jsontext(ARRAY['age', 'sex'], ARRAY['21', 'female']);
         to_jsontext         
-----------------------------
 {"age":"21","sex":"female"}