Saturday, March 9, 2013

Loading Useful Modules in PLV8

PLV8 is a trusted language. In fact it arguably safer than pretty much any non-core loadable language, since the interpreter is naturally sandboxed. So there's no way to load external processing modules from the file system. However, pure JavaScript modules can be cleanly loaded from the database itself. A couple of extremely useful ones I have found are Underscore and node-jPath. The former can be loaded with no modification whatever. The latter is written for use with node.js and needs a small amount of adjustment before it's loaded in plv8.

So how do you do this? Well, first you need to grab the code:
curl -o underscore.js http://underscorejs.org/underscore-min.js
curl -o jpath.js https://raw.github.com/stsvilik/node-jpath/master/lib/node-jpath.js
Then edit jpath.js so that, at the bottom, instead of assigning to module.exports it creates an object called jpath and adds the select and filter methods to it.

Then you'll need a table to load the modules from, and to put the modules in this table. You'll also need a function to load them. These can be accomplished with the following psql script:
\set underscore `cat underscore-min.js`
\set jpath `cat jpath.js`

create table plv8_modules(modname text primary key, load_on_start boolean, code text);

insert into plv8_modules values ('underscore',true,:'underscore'),
    ('jpath',true,:'jpath');

create or replace function plv8_startup()
returns void
language plv8
as
$$
load_module = function(modname)
{
    var rows = plv8.execute("SELECT code from plv8_modules " +
                            " where modname = $1", [modname]);
    for (var r = 0; r < rows.length; r++)
    {
        var code = rows[r].code;
        eval("(function() { " + code + "})")();
    }
        
};
$$;

Now test it out:
select plv8_startup();
do language plv8 ' load_module("underscore"); load_module("jpath"); ';
-- test the underscore module's extend function
do language plv8 $$ 
   x = {'a':1}; 
   y=_.extend(x,{'a':2,'b':3},{'b':4,'c':5}); 
   plv8.elog(NOTICE,JSON.stringify(y)); 
$$;
-- test jpath module's filter function
do language plv8 $$
  var jsonData = {
    people: [
        {name: "John", age:26, gender:"male"},
        {name: "Steve", age:24, gender:"male"},
        {name: "Susan", age:22, gender:"female"},
        {name: "Linda", age:30, gender:"female"},
        {name: "Adam", age:32, gender:"male"}
    ]
  };
  //We want to get all males younger then 25
  var match = jpath.filter(jsonData, "people[gender=male && age < 25]");
  plv8.elog(NOTICE,JSON.stringify(match));
$$;
You should see output like:
NOTICE:  {"a":2,"b":4,"c":5}
DO
NOTICE:  [{"name":"Steve","age":24,"gender":"male"}]
DO
Now, the final step is to make these get loaded automatically. Alter your plv8_startup() function by adding to following at the end:
// now load all the modules marked for loading on start
var rows = plv8.execute("SELECT modname, code from plv8_modules where load_on_start");
for (var r = 0; r < rows.length; r++)
{
 var code = rows[r].code;
 eval("(function() { " + code + "})")();
}
and finally add
plv8.start_proc = 'plv8_startup'
to you postgresql.conf file, and restart. You should now be able to use these modules anywhere in your PLv8 code.


20 comments:

  1. Using Extension Templates it's going to be even easier shipping those extra JS files in 9.3, and with the Event Triggers capabilities it will even be possible to include the whole fetching and template installation as part of the CREATE EXTENSION command... stay tuned :)

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. sorry, not working only
    plv8.start_proc = 'plv8_startup'
    in postgresql.conf!

    Thanks for the great idea! :)

    ReplyDelete
  4. Hmm, it looks like you need to mangle jpath some to make it postgres friendly. I must have forgotten that when I wrote this post.

    Thanks for letting me know. Maybe I will fork it for use with PLV8.

    ReplyDelete
    Replies
    1. Actually, I did mention that in the post, and you seem to have missed it: "Then edit jpath.js so that, at the bottom, instead of assigning to module.exports it creates an object called jpath and adds the select and filter methods to it."

      Delete
  5. also this: for (var r = 0; r > rows.length; r++)

    should be: for (var r = 0; r < rows.length; r++)

    ReplyDelete
  6. You might want to look at this project. It's less painful. It includes unit testing, migrations, logging, bundling ... https://github.com/mgutz/plv8-bedrock

    ReplyDelete
  7. Hi Andrew,
    I used some of the code as reference for an example from a book about Underscore.js that I am working on. I hope that is ok - you can browse my code version at https://github.com/popalexandruvasile/underscorejs-examples/blob/master/browser-server-database/postgresql-underscore/insertSeedData.sql
    Thank you for you blog post I could not have linked Underscore to PostgreSQL without it.

    ReplyDelete
  8. Based on this article, I've made this extension : https://github.com/slardiere/plv8_modules

    ReplyDelete
  9. This is great. Do you happen to have any suggestions how to load this automatically on startup and have it available on all connections? For some reason, anytime I make a new connection to the database (from the application or the command line), it acts like the load_module function hasn't been run. If I run it manually, it works for that connection, but doesn't for others. Thanks!

    ReplyDelete
    Replies
    1. You set it in via plv8.start_proc in postgresql.conf, as the post says.

      Delete
  10. I use the instance of postgresql provided from Amazon RDS, so I can't edit `postgresql.conf` to add plv8.start_proc = 'plv8_startup'', is there some other way to do this?

    ReplyDelete
    Replies
    1. Setting it on the database via ALTER DATABASE might work. Try it and see.

      Delete
    2. Just for the record: yes it does: "ALTER DATABASE dbname SET plv8.start_proc = 'plv8_startup';"

      Delete
    3. @Maurits were you saying that works in Amazon RDS? I've never been able to run ALTER DATABASE commands in RDS. Always get a permission error. I assumed it was because of the locked down nature of RDS.

      Delete
    4. Yeah. I just tried and got a permissions error. I don't know any other way, sorry.

      Delete
  11. currently it is not clear where i should store grabbed js code? in which location?

    ReplyDelete