Installing PL/Ruby for PostgreSQL 8.3

Question

This is to enable the development of postgres functions with embedded ruby code, but I have been unable to build it.

As advised by http://www.robbyonrails.com/articles/2005/08/22/installing-untrusted-pl-ruby-for-postgresql

I am trying to build the needed plruby.so from the latest version (plruby-0.5.3.tar.gz) provided at ftp://moulon.inra.fr/pub/ruby/

I've sorted out where my local postgres set up is and adjusted the invocation to:

ruby extconf.rb --with-pgsql-include=/usr/postgresql-8.3.4/include/server --enable-shared --disable-conversion --with-pgsql-version=83

I've tried quite number of variations on that, but it does not seem to be able to successfully make the 'conftest.c' file

It says this:

checking for catalog/pg_proc.h... yes
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

And here is what I end up with in my mkmf.log

have_header: checking for catalog/pg_proc.h... -------------------- yes
"gcc -E -I. -I/usr/lib/ruby/1.8/x86_64-linux -I. -I/usr/postgresql-8.3.4/include/server   -g -O2  -fPIC  conftest.c -o conftest.i"
checked program was:
/* begin */
1: #include <catalog/pg_proc.h>
/* end */

When I run the gcc line manually, it says there is no 'conftest.c' (and there is not, but it is supposed to be generated).

'uname -a' ... gives
Linux vdev1 2.6.18.8-xen #2 SMP Thu May 8 11:52:29 PDT 2008 x86_64 x86_64 x86_64 GNU/Linux
'ruby -v' ... gives
ruby 1.8.6 (2008-08-11 patchlevel 287) [x86_64-linux]

Any help and/or advice would be appreciated.

-- Mike Berrow

Answer 1

OK, I managed to hand build this (bypassing the fragile extconf.rb and makefile) by googling for a logfile of a successful build, starting with the gcc lines I saw there, then fiddling with the gcc compile flags and paths until it worked.

In plruby.h change the SAFE_LEVEL to 0 as shown below

#ifndef SAFE_LEVEL
//#define SAFE_LEVEL 12
#define SAFE_LEVEL 0
#endif

Compile each from shell then link

gcc -I. -I. -I/usr/lib/ruby/1.8/x86_64-linux -I. -DHAVE_CATALOG_PG_PROC_H -DHAVE_RB_HASH_DELETE -DHAVE_ST_H -DHAVE_UTILS_ARRAY_H -I/usr/postgresql-8.3.4/include/server  -D_FILE_OFFSET_BITS=64  -fPIC -fno-strict-aliasing -g -g -O2  -fPIC  -DHAVE_RB_HASH_DELETE -DHAVE_RB_INITIALIZE_COPY -DPG_UTILS_ARRAY -DPG_PL_TRYCATCH -DPG_PL_VERSION=83 -DPLRUBY_CALL_HANDLER=plruby_call_handler -DPLRUBY_VALIDATOR=plruby_validator  -c plruby.c
gcc -I. -I. -I/usr/lib/ruby/1.8/x86_64-linux -I. -DHAVE_CATALOG_PG_PROC_H -DHAVE_RB_HASH_DELETE -DHAVE_ST_H -DHAVE_UTILS_ARRAY_H -I/usr/postgresql-8.3.4/include/server  -D_FILE_OFFSET_BITS=64  -fPIC -fno-strict-aliasing -g -g -O2  -fPIC  -DHAVE_RB_HASH_DELETE -DHAVE_RB_INITIALIZE_COPY -DPG_UTILS_ARRAY -DPG_PL_TRYCATCH -DPG_PL_VERSION=83 -DPLRUBY_CALL_HANDLER=plruby_call_handler -DPLRUBY_VALIDATOR=plruby_validator  -c plplan.c
gcc -I. -I. -I/usr/lib/ruby/1.8/x86_64-linux -I. -DHAVE_CATALOG_PG_PROC_H -DHAVE_RB_HASH_DELETE -DHAVE_ST_H -DHAVE_UTILS_ARRAY_H -I/usr/postgresql-8.3.4/include/server  -D_FILE_OFFSET_BITS=64  -fPIC -fno-strict-aliasing -g -g -O2  -fPIC  -DHAVE_RB_HASH_DELETE -DHAVE_RB_INITIALIZE_COPY -DPG_UTILS_ARRAY -DPG_PL_TRYCATCH -DPG_PL_VERSION=83 -DPLRUBY_CALL_HANDLER=plruby_call_handler -DPLRUBY_VALIDATOR=plruby_validator  -c plpl.c
gcc -I. -I. -I/usr/lib/ruby/1.8/x86_64-linux -I. -DHAVE_CATALOG_PG_PROC_H -DHAVE_RB_HASH_DELETE -DHAVE_ST_H -DHAVE_UTILS_ARRAY_H -I/usr/postgresql-8.3.4/include/server -D_FILE_OFFSET_BITS=64  -fPIC -fno-strict-aliasing -g -g -O2  -fPIC  -DHAVE_RB_HASH_DELETE -DHAVE_RB_INITIALIZE_COPY -DPG_UTILS_ARRAY -DPG_PL_TRYCATCH -DPG_PL_VERSION=83 -DPLRUBY_CALL_HANDLER=plruby_call_handler -DPLRUBY_VALIDATOR=plruby_validator  -c pltrans.c
gcc -shared -o plruby.so plruby.o plplan.o plpl.o pltrans.o -L. -L/usr/lib -L/usr/postgresql-8.3.4/lib -L. -Wl,-Bsymbolic -rdynamic -Wl,-export-dynamic -lruby  -lpthread -ldl -lcrypt -lm -lc

Place the '.so' file built above in the dynamic library path ($libdir) [ determined using pg_config --pkglibdir giving (in my case) /usr/postgresql-8.3.4/lib ]

Others taking this approach will most likely have to do their own tweaking.

Add these functions ...

CREATE OR REPLACE FUNCTION plruby_call_handler()
  RETURNS language_handler AS
'$libdir/plruby', 'plruby_call_handler'
  LANGUAGE 'c' VOLATILE
  COST 1;
ALTER FUNCTION plruby_call_handler() OWNER TO postgres;

CREATE OR REPLACE FUNCTION plruby_validator(oid)
  RETURNS void AS
'$libdir/plruby', 'plruby_validator'
  LANGUAGE 'c' VOLATILE
  COST 1;
ALTER FUNCTION plruby_validator(oid) OWNER TO postgres;

Add 'plruby' as a procedural language

CREATE PROCEDURAL LANGUAGE 'plruby' HANDLER plruby_call_handler;

Test it:

CREATE FUNCTION ruby_max(int4, int4) RETURNS text AS '
    if args[0].to_i > args[1].to_i
        return "The one on the left is bigger"
    else
        return "The one on the right is bigger"
    end
' LANGUAGE 'plruby';

select ruby_max(8, 9);

There are other build options for this that enable type 'conversions'. The above build is the simplest one and all function parameters actually come into ruby as strings (even though they are declared as int4). Thus the need for the 'to_i' call here.