Installing Pl/Ruby for Postgresql 8.3

Installing PL/Ruby for PostgreSQL 8.3

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.

Ruby tutorial for how to write stored procedures for PostgreSQL?

Check this website: http://moulon.inra.fr/ruby/plruby.html , it has some nice examples.

PostgreSQL / Ruby for commercial application

You will do fine with either EnterpriseDB or PostgreSQL. EnterpriseDB might have a better Windows install experience, but on Linux installing vanilla PostgreSQL is a very easy process...most distros provide it out of the box. The real benefit of EnterpriseDB over PostgreSQL is Oracle PL/SQL support, some upstream improvements, and commercial support. However, you can purchase commercial support from them for vanilla PostgreSQL as well.

We use vanilla PostgreSQL 8.3 in a large (800 kloc) production ERP system. It handles it extremely well. We also use 8.4 in a number of other applications, including two Rails apps. In my opinion, you can't go wrong with PostgreSQL, and you'll be very pleased with PostgreSQL and Rails.

Regarding IDE and RoR variant...well...assuming you mean RoR version, they all support PostgreSQL. I'd personally start with the 3.0 beta, since it's the way of the future. Regarding an IDE, Netbeans is probably the best free option out there. RubyMine would be great if it weren't so damned buggy. Me, I use vim.

PostgreSQL replication strategies

There are a few tools for master-slave (and master-multislave) scenarios, usually trigger-based. Slony-I has already been mentioned (is stable and solid, but a bit difficult to operate). People having problems with Slony-I wrote
Londiste (by Skype team) and PyReplica. Bah, and I just spotted
Mammoth has been open-sourced

For multimaster there is Bucardo (note: it is not that polished)
or commercial offerings - for example by Continuent or CyberTec.

rails3, postgresql & postGIS: frozen in migrations

It took a long while to find the culprit. Hopefully this can be helpful to anyone installing postGIS and who experiments leading to changes in migrations.

Fundamentally, to add a variable indexed via postGIS and postgre, as such:

add_column :structures, :lonlat, :point, :geographic => true
add_index  :structures, :lonlat, using: 'GIST'

an initialiser (UK spelling) needs to be invoked

Rails.application.config.after_initialize do
  ActiveRecord::Base.connection_pool.disconnect!

  ActiveSupport.on_load(:active_record) do
    config = Rails.application.config.database_configuration[Rails.env]
    config['adapter'] = 'postgis'
    ActiveRecord::Base.establish_connection(config)
  end
end

This initialiser has to be added to the initializer folder just prior to the migration that creates/destroys the first/last point column. Otherwise the error pops up.

This is tested and functions in both forward and reverse migrations.

I can see where this solution gets messy with multiple installations in development mode.

Any better solution welcome!

Why can't I save any models to PostgreSQL database in Rails?

Have you added any validations since that object was created? Try this:

> m = Model.find(1)
> m.valid?
> m.errors.full_messages

If something was wrong with your PostgreSQL database you'd probably be seeing exceptions and read errors.

---

Related Topics