PL/v8 is now the richest procedure language

It's been months since the latest version of PL/v8 was released. Today I'm happy to announce the new releases of PL/v8 1.2.1, and 1.3.0. 1.2.1 contains a couple of bug fixes upon 1.2.0, and 1.3.0 has new features and backward incompatibility. Especially json type conversion between DB and v8 is considered as a major backward compatibility, for which I suggest staying in 1.2.1 for a while if you have json argument function, but strongly recommend migrating to 1.3.0 soon.

Let's look through major items in 1.3.0.

Make two dialects (plcoffee, plls) official sub-extensions.

I have already introduced plcoffee in my posts, but later on Audrey Tang, who is also the author of Pugs, came up with a patch to add LiveScript support along with CoffeeScript, and I made them official sub-extensions. If you build PL/v8 and run `make install`, you'll be able to do


And they're ready.

Implement builtin json type conversion.

This has been one of the top demands for PL/v8. Although we cannot store v8's objects in efficient way, we can serialize them via JSON.parse()/stringify(). In the previous version, users needed to call those functions to convert json types in/out, but now PL/v8 does it, if it's with PostgreSQL 9.2 or above. This will be painless when it comes to pure JS functions using find_function, as it's more transparent to the callee function.

Static build and automatic v8 build via 'static' target.

If you type `make static` in the plv8 source directory, it automatically downloads v8 source and build it, and statically link to v8. This may mitigate the v8 version confusion and build annoyance.

Implement v8's remote debugger if enabled.

d8, the v8's command line tool, supports remote debugger. If you build PL/v8 with ENABLE_DEBUGGER_SUPPORT, the module will open remote debugger port which d8 can connect and control PL/v8. Once it's ready, try d8 such like:

$ d8 --remote-debugger --debugger-port=35432

The `debugger` statement can be also used to set a break point from JS code.

Implement bytea type conversion and typed array.

The bytea type was not automatically converted to JS value. Actually, v8 has a good way to handle this; typed array. It has been in v8 for pixel memory in a good performance and Node.js also uses it. The concept of typed array is it can map byte array to JS array directly without creating each element as JS value. What it means is when you pass bytea type to PL/v8 function, the value in the function can be seen as an array and you can fetch unsigned byte in each element in efficient way. Also this allows primitive PostgreSQL arrays such like int2, int4 to be mapped to JS array without creating a bunch of JS element. To use it, you need to declare the argument as plv8_int4array domain type which is defined by plv8 extension. One example is this.

create table foo(i, ary) as
  select i, ary from(select array_agg(a) ary
  from generate_series(1, 100000)a)a, generate_series(1, 100)i;

create or replace function int4sum(ary plv8_int4array) returns int8 as $$
  var sum = 0;
  for (var i = 0; i < ary.length; i++) {
    sum += i;
  return sum;
$$ language plv8 immutable strict;

select int4sum(ary) from foo;
Time: 48.882 ms

-- when using plv8_int4array instead of int4[]
Time: 820.785 ms

The caveat is the array must be a 1-dimensional non-NULL array, but if you store some numeric values in array column, this must save a lot of time.

Allow polymorphic and internal types in argument and return types.

Someone has complained this before, but now we support anyelement, anyarray types as argument and return types. Also, you can declare internal type as argument, which is very meaningful when you declare a pure JS function for find_function.

Support user defined window functions.

PL/v8 is the first procedure language that allows you full access to window function API (although PL/R has also supported user-defined window functions partially). With window functions, you can create a function that operates on multiple rows that fall into a specific set of rows. To use it, retrieve WindowObject via plv8.get_window_object() which provides wrapper functions for internal C-level window function APIs.

var winobj = plv8.get_window_object();
return winobj.get_current_position() + 1;

I implemented all the built-in window functions in PL/v8, which you can see in the regression test file.

      • -

PL/v8 has been a trusted, full-functional procedure language even in the previous version. As you can see from this post, it's now the richest procedure language among all the existing ones including PL/pgSQL or PL/python. If you consider a trusted language, the only options are PL/pgSQL which is slow and you can write window functions with it. If you use mathematical libraries, PL/python is a good choice, but it's not trusted language, and array access in PL/v8 is much faster than PL/python. So, of course it is your choice, but PL/v8 is absolutely the first language you should consider when you have to write something.