PLV8 is a trusted Javascript language extension for PostgreSQL. It can be used for stored procedures, triggers, etc.
PLV8 works with most versions of Postgres, version 13
and above.
If the PLV8 extension has been installed to your system, the PLV8 extension can be installed into your PostgreSQL database by running:
=# CREATE EXTENSION plv8;
You can verify the installation in two ways. As of PLV8 2.0.0
, you can
execute a stored procedure:
=# SELECT plv8_version();
Alternately, you can run the following on all versions of PLV8:
=# DO $$ plv8.elog(NOTICE, plv8.version); $$ LANGUAGE plv8;
As of PLV8 version 2.3.3
, you can use upgrade scripts to upgrade your
installation from any version higher than 1.5.0
:
=# ALTER EXTENSION plv8 UPDATE TO `3.2.0`;
Note that until the database has been restarted, the old version of PLV8 will
still be loaded, though SELECT plv8_version()
will return the new version.
This is an artifact of how Postgres manages extensions.
Updating PL/v8 is usually straightforward as it is a small and stable extension
The procedure that is responsible for invoking this installation script
(generated during compile time based on plv8.sql.common), is controlled by
PostgreSQL and runs when CREATE EXTENSION is executed only. After building, it
takes the form of plv8--/usr/share/postgresql/<PG_MAJOR>/extension
, depending on the OS.
When this command is executed, PostgreSQL tracks which objects belong to the
extension and conversely removes them upon uninstallation, i.e., whenever
DROP EXTENSION
is called.
You can explore some of the objects that PL/v8 stores under PostgreSQL:
=# SELECT lanname FROM pg_catalog.pg_language WHERE lanname = 'plv8';
=# SELECT proname FROM pg_proc p WHERE p.proname LIKE 'plv8%';
=# SELECT typname FROM pg_catalog.pg_type WHERE typname LIKE 'plv8%';
To update PostgreSQL, you can DROP
the existing extension:
=# DROP EXTENSION plv8;
Install the new version, and CREATE
the extension:
=# CREATE EXTENSION plv8;
Alternately, you can backup and restore your database.
In PLV8, each session has one global JS runtime context. This enables function invocations at low cost, and sharing common object among the functions. However, for the security reasons, if the user switches to another with SET ROLE command, a new JS runtime context is initialized and used separately. This prevents the risk of unexpected information leaking.
Each plv8
function is invoked as if the function is the property of other
object. This means this
in each function is a Javascript object
that is created
every time the function is executed in a query. In other words, the life time and
the visibility of the this
object in a function is only a series of function calls in
a query. If you need to share some value among different functions, keep it in the
global plv8
object because each function invocation has a different this
object.
PLV8 provides a start up facility, which allows you to call a plv8
runtime
environment initialization function specified in the GUC variable. This can
only be set by someone with administrator access to the database you are
accessing.
SET plv8.start_proc = 'plv8_init';
SELECT plv8_test(10);
If this variable is set when the runtime is initialized, before the function
call of plv8_test()
another plv8
function plv8_init()
is invoked. In such
initialization function, you can add any properties to plv8
object to expose
common values or assign them to the this property. In the initialization function,
the receiver this is specially pointing to the global object, so the variables
that are assigned to the this property in this initialization are visible from
any subsequent function as global variables.
Remember CREATE FUNCTION
also starts the plv8
runtime environment, so make
sure to SET
this GUC before any plv8
actions including CREATE FUNCTION
.
There are a couple of utility procedures that will get installed.
These are useful for long-running connections from various backend services.
Get information about all the current running environments on a specific connection from all users.
Can be run by superuser only.
SELECT plv8_info();
Outputs JSON
[
{
"user": "user1",
"total_heap_size": 1327104,
"total_physical_size": 474336,
"used_heap_size": 386680,
"heap_size_limit": 270008320,
"external_memory": 0,
"number_of_native_contexts": 2,
"contexts": []
},
{
"user": "user2",
"total_heap_size": 1327104,
"total_physical_size": 474336,
"used_heap_size": 386680,
"heap_size_limit": 270008320,
"external_memory": 0,
"number_of_native_contexts": 3,
"contexts": ["my context"]
}
]
Note: "number_of_native_contexts" = "contexts".length + 2
Reset user isolate or context
To reset a specific context run:
SELECT plv8_reset('my context');
Will reset the context and re-create globalThis
on a next invocation of that context.
To reset all contexts and reboot the whole environment of a specfifc user:
SELECT plv8_reset();
Superusers can kill a specific user environment by impersonating the user:
SET ROLE "some_user";
SELECT plv8_reset();
RESET ROLE;
Building PLV8 for MacOS or Linux has some specific requirements:
The following packages must be installed to build on Ubuntu or Debian:
libtinfo5
build-essential
pkg-config
libstdc++-12-dev
(depending on version, may be 10 instead of 12)cmake
git
The following packages must be installed to build on EL9 or EL8:
cmake
git
Note that some distributions of Linux may have additional requirements. This is not meant to be an exhaustive list.
The following packages must be install to build on MacOS:
XCode
- and the command line toolscmake
Downloading the source code is very straightforward:
$ git clone https://github.com/plv8/plv8
$ cd plv8
$ make
Building is simple:
$ make
This will download v8
and compile it as well.
| Note: If you have multiple versions of PostgreSQL installed like 9.5 and 9.6, Plv8 will only be built for PostgreSQL 9.6. This is because make calls pg_config to get the version number, which will always be the latest version installed. If you need to build Plv8 for PostgreSQL 9.5 while you have 9.6 installed pass make the PG_CONFIG variable to your 9.5 version of pg_config. This works for make
, and make install
. For example in Ubuntu:
$ make PG_CONFIG=/usr/lib/postgresql/13/bin/pg_config
Plv8 allows you to optionally build with an execution timeout for Javascript functions, when enabled at compile-time.
$ make EXECUTION_TIMEOUT=1
By default, the execution timeout is not compiled, but when configured it has a
timeout of 300 seconds (5 minutes). You can override this by setting the
plv8.execution_timeout
variable. It can be set between 1
second and 65536
seconds, but cannot be disabled.
Once PLV8 has been built, you need to install it for PostgreSQL to be able to use it:
$ make install
This might require root
access, depending on how PostgreSQL is installed:
$ sudo make install
Once PLV8 is installed, you can verify the install by running:
$ make installcheck
PLV8 has some configuration variables that can be set either in postgresql.conf
or at runtime using SET
.
Variable | Description | Default |
---|---|---|
plv8.start_proc |
PLV8 function to run once when PLV8 is first used | none |
plv8.icu_data |
ICU data file directory (when compiled with ICU support) | none |
plv8.v8_flags |
V8 engine initialization flags (e.g. --harmony for all current harmony features) | none |
plv8.execution_timeout |
V8 execution timeout (when compiled with EXECUTION_TIMEOUT) | 300 seconds |
plv8.boot_proc |
Like start_proc above, but can be set by superuser only |
none |
plv8.memory_limit |
Memory limit for the per-user heap usage on each connection, in MB | 256 |
plv8.context |
Users can switch to a different global object (globalThis ) by using an arbitrary context string |
none |
plv8.context_cache_size |
Size of the per-user LRU cache for custom contexts | 8 |
plv8.max_eval_size |
Control how eval() can be used, -1 = no limits, 0 = eval() disabled, any other number = max length of the eval-able string in bytes |
2MB |
PLV8 has the ability to execute multiple types of function calls inside of PostgreSQL.
In PLV8, you can write your invoked function call in Javascript, using the usual
CREATE FUNCTION
statement. Here is an example of a scalar
function call:
CREATE FUNCTION plv8_test(keys TEXT[], vals TEXT[]) RETURNS JSON AS $$
var o = {};
for(var i=0; i<keys.length; i++){
o[keys[i]] = vals[i];
}
return o;
$$ LANGUAGE plv8 IMMUTABLE STRICT;
=# SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']);
plv8_test
---------------------------
{"name":"Tom","age":"29"}
(1 row)
Internally, the function will defined such as:
(function(keys, vals) {
var o = {};
for(var i=0; i<keys.length; i++){
o[keys[i]] = vals[i];
}
return o;
})
Where keys
and vals
are type checked and validated inside of PostgreSQL,
called as arguments to the function, and o
is the object that is returned as
the JSON
type back to PostgreSQL. If argument names are omitted in the creation
of the function, they will be available in the function as $1
, $2
, etc.
PLV8 supports returning SET
from function calls:
CREATE TYPE rec AS (i integer, t text);
CREATE FUNCTION set_of_records() RETURNS SETOF rec AS
$$
// plv8.return_next() stores records in an internal tuplestore,
// and return all of them at the end of function.
plv8.return_next( { "i": 1, "t": "a" } );
plv8.return_next( { "i": 2, "t": "b" } );
// You can also return records with an array of JSON.
return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ];
$$
LANGUAGE plv8;
Running this gives you a SETOF
result:
=# SELECT * FROM set_of_records();
i | t
---+---
1 | a
2 | b
3 | c
4 | d
(4 rows)
Internally, if the function is declared as RETURNS SETOF
, PLV8 prepares a
tuplestore
every time every time it is called. You can call the
plv8.return_next()
function as many times as you need to return a row. In
addition, you can also return an array
to add a set of records.
If the argument object to return_next()
has extra properties that are not
defined by the argument, return_next()
raises an error.
PLV8 supports trigger function calls:
CREATE FUNCTION test_trigger() RETURNS TRIGGER AS
$$
plv8.elog(NOTICE, "NEW = ", JSON.stringify(NEW));
plv8.elog(NOTICE, "OLD = ", JSON.stringify(OLD));
plv8.elog(NOTICE, "TG_OP = ", TG_OP);
plv8.elog(NOTICE, "TG_ARGV = ", TG_ARGV);
if (TG_OP == "UPDATE") {
NEW.i = 102;
return NEW;
}
$$
LANGUAGE "plv8";
CREATE TRIGGER test_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON test_tbl FOR EACH ROW
EXECUTE PROCEDURE test_trigger('foo', 'bar');
If the trigger type is an INSERT
or UPDATE
, you can assign properties of
NEW
variable to change the actual tuple stored by this operation. A PLV8
trigger function will have the following special arguments that contain the
trigger state:
NEW
OLD
TG_NAME
TG_WHEN
TG_LEVEL
TG_OP
TG_RELID
TG_TABLE_NAME
TG_TABLE_SCHEMA
TG_ARGV
For more information see the trigger section in PostgreSQL manual.
PLV8 supports the DO
block when using PostgreSQL 9.0 and above:
DO $$ plv8.elog(NOTICE, 'this', 'is', 'inline', 'code'); $$ LANGUAGE plv8;
For the result and arguments, PostgreSQL types and Javascript types are mapped automatically, if the desired PostgreSQL type is one of:
OID
bool
INT3
INT4
INT8
FLOAT4
FLOAT8
NUMERIC
DATE
TIMESTAMP
TIMESTAMPTZ
BYTEA
JSON
(>= 9.2)JSONB
(>= 9.4)and the Javascript value looks compatible, then the conversion succeeds.
Otherwise, PLV8 tries to convert them via the cstring
representation. An
array
type is supported only if the dimension is one. A Javascript object
will be mapped to a tuple
when applicable. In addition to these types, PLV8
supports polymorphic types such like ANYELEMENT
and ANYARRAY
. Conversion of
BYTEA
is a little different story. See the TypedArray section.
The typed array
is something v8
provides to allow fast access to native
memory, mainly for the purpose of their canvas support in browsers. PLV8 uses
this to map BYTEA
and various array types to a Javascript array
. In the case
of BYTEA
, you can access each byte as an array of unsigned bytes. For
int2
/int4
/float4
/float8
array types, PLV8 provides direct access to each
element by using PLV8 domain types.
plv8_int2array
maps int2[]
plv8_int4array
maps int4[]
plv8_float4array
maps float4[]
plv8_float8array
maps float8[]
These are only annotations that tell PLV8 to use the fast access method instead
of the regular one. For these typed arrays, only 1-dimensional arrays without
any NULL
elements. There is currently no way to create such typed array inside
PLV8 functions, only arguments can be typed array. You can modify the element and
return the value. An example for these types are as follows:
CREATE FUNCTION int4sum(ary plv8_int4array) RETURNS int8 AS $$
var sum = 0;
for (var i = 0; i < ary.length; i++) {
sum += ary[i];
}
return sum;
$$ LANGUAGE plv8 IMMUTABLE STRICT;
SELECT int4sum(ARRAY[1, 2, 3, 4, 5]);
int4sum
---------
15
(1 row)
PLV8 includes a number of built-in functions bound to the plv8
object for you
to use.
plv8.elog
plv8.elog
emits a message to the client or the PostgreSQL log file. The
emit level is one of:
DEBUG5
DEBUG4
DEBUG3
DEBUG2
DEBUG1
LOG
INFO
NOTICE
WARNING
ERROR
var msg = 'world';
plv8.elog(DEBUG1, 'Hello', `${msg}!`);
See the PostgreSQL manual for information on each error level.
plv8.quote_literal
, plv8.nullable
, plv8.quote_ident
Each function for the quote family is identical to the built-in SQL function with the same name.
plv8.find_function
PLV8 provides a function to access other functions defined as plv8
functions
that have been registered in the database.
CREATE FUNCTION callee(a int) RETURNS int AS $$ return a * a $$ LANGUAGE plv8;
CREATE FUNCTION caller(a int, t int) RETURNS int AS $$
var func = plv8.find_function("callee");
return func(a);
$$ LANGUAGE plv8;
With plv8.find_function()
, you can look up other PLV8 functions. If they
are not a PLV8 function, and error is thrown. The function signature parameter
to plv8.find_function()
is either of regproc
(function name only) or
regprocedure
(function name with argument types). You can make use of the
internal type for arguments and void type for return type for the pure Javascript
function to make sure any invocation from SQL statements should not occur.
plv8.version
The plv8
object provides a version string as plv8.version
. This string
corresponds to the plv8
module version.
plv8.memory_usage
You can get your own memory usage by calling plv8.memory_usage()
with no params.
The resulting object looks like this:
{
"total_heap_size":1327104,
"total_physical_size":472712,
"used_heap_size":381748,
"heap_size_limit":270008320,
"external_memory":0,
"number_of_native_contexts":2
}
See nodejs v8.getHeapStatistics()
plv8.run_script
Run a script from source code, it's like eval()
but takes a second argument: script name
Can be pretty useful for debugging
Can be used like this
const sourceCode = `globalThis.myFunc = () => 42`
try {
plv8.run_script(sourceCode, 'myScript.js')
myFunc()
} catch (e) {
plv8.elog(NOTICE, e.message)
}
PLV8 provides functions for database access, including prepared statements, and cursors.
plv8.execute
plv8.execute(sql [, args])
Executes SQL statements and retrieves the results. The sql
argument is
required, and the args
argument is an optional array
containing any arguments
passed in the SQL query. For SELECT
queries, the returned value is an array
of objects
. Each object
represents one row, with the object
properties
mapped as column names. For non-SELECT
queries, the return result is the
number of rows affected.
var json_result = plv8.execute('SELECT * FROM tbl');
var num_affected = plv8.execute('DELETE FROM tbl WHERE price > $1', [ 1000 ]);
plv8.prepare
plv8.prepare(sql [, typenames])
Opens or creates a prepared statement. The typename
parameter is an array
where each element is a string
that corresponds to the PostgreSQL type name
for each bind
parameter. Returned value is an object of the PreparedPlan
type.
This object must be freed by plan.free()
before leaving the function.
var plan = plv8.prepare('SELECT * FROM tbl WHERE col = $1', [ 'int' ]);
var rows = plan.execute([ 1 ]);
var sum = 0;
for (var i = 0; i < rows.length; i++) {
sum += rows[i].num;
}
plan.free();
return sum;
PreparedPlan.execute
PreparedPlan.execute([ args ])
Executes the prepared statement. The args
parameter is the same as what would be
required for plv8.execute()
, and can be omitted if the statement does not have
any parameters. The result of this method is also the same as plv8.execute()
.
PreparedPlan.cursor
PreparedPlan.cursor([ args ])
Opens a cursor form the prepared statement. The args
parameter is the same as
what would be required for plv8.execute()
and PreparedPlan.execute()
. The
returned object is of type Cursor
. This must be closed by Cursor.close()
before leaving the function.
var plan = plv8.prepare('SELECT * FROM tbl WHERE col = $1', [ 'int' ]);
var cursor = plan.cursor([ 1 ]);
var sum = 0, row;
while (row = cursor.fetch()) {
sum += row.num;
}
cursor.close();
plan.free();
return sum;
PreparedPlan.free
Frees the prepared statement.
Cursor.fetch
Cursor.fetch([ nrows ])
When the nrows
parameter is omitted, fetches a row from the cursor and returns
it as an object
(note: not as an array
). If specified, fetches as many rows
as the nrows
parameter, up to the number of rows available, and returns an
array
of objects
. A negative value will fetch backward.
Cursor.move
Cursor.move(nrows)
Moves the cursor nrows
. A negative value will move backward.
Cursor.close
Closes the Cursor
.
plv8.subtransaction
plv8.subtransaction(func)
plv8.execute()
creates a subtransaction each time it executes. If you need
an atomic operation, you will need to call plv8.subtransaction()
to create
a subtransaction block.
try{
plv8.subtransaction(function(){
plv8.execute("INSERT INTO tbl VALUES(1)"); // should be rolled back!
plv8.execute("INSERT INTO tbl VALUES(1/0)"); // occurs an exception
});
} catch(e) {
... execute fall back plan ...
}
If one of the SQL execution in the subtransaction block fails, all of operations
within the block are rolled back. If the process in the block throws a Javascript
exception, it is carried forward. So use a try ... catch
block to capture it and
do alternative operations if it occurs.
You can define user-defined window functions with PLV8. It wraps the C-level
window function API to support full functionality. To create one, first obtain a
window object by calling plv8.get_window_object()
, which provides the following
interfaces:
WindowObject.get_current_position
Returns the current position in the partition, starting from 0
.
WindowObject.get_partition_row_count
Returns the number of rows in the partition.
WindowObject.set_mark_position
WindowObject.set_mark_position(pos)
Sets the mark at the specified row. Rows above this position will be gone and no longer accessible later.
WindowObject.rows_are_peers
WindowObject.rows_are_peers(pos1, pos1)
Returns true
if the rows at pos1
and pos2
are peers.
WindowObject.get_func_arg_in_partition
WindowObject.get_func_arg_in_partition(argno, relpos, seektype, mark_pos)
WindowObject.get_func_arg_in_frame
WindowObject.get_func_arg_in_frame(argno, relpos, seektype, mark_pos)
Returns the value of the argument in argno
(starting from 0) to this function
at the relpos
row from seektype
in the current partition or frame.
seektype
can be either of WindowObject.SEEK_HEAD
, WindowObject.SEEK_CURRENT
,
or WindowObject.SEEK_TAIL
. If mark_pos
is true
, the row the argument is
fetched from is marked. If the specified row is out of the partition/frame, the
returned value will be undefined.
WindowObject.get_func_arg_in_current
WindowObject.get_func_arg_in_current(argno)
Returns the value of the argument in argno
(starting from 0) to this function
at the current row. Note that the returned value will be the same as the argument
variable of the function.
WindowObject.get_partition_local
WindowObject.get_partition_local([ size ])
Returns partition-local value, which is released at the end of the current partition. If nothing is stored, undefined is returned. size argument (default 1000) is the byte size of the allocated memory in the first call. Once the memory is allocated, the size will not change.
WindowObject.set_partition_local
WindowObject.set_partition_local(obj)
Stores the partition-local value, which you can retrieve later with
get_partition_local()
. This function internally uses JSON.stringify()
to serialize the object, so if you pass a value that is not able to be serialized
it may end up being an unexpected value. If the size of a serialized value is
more than the allocated memory, it will throw an exception.
You can also learn more on how to use these API in the sql/window.sql
regression test, which implements most of the native window functions. For
general information on the user-defined window function, see the CREATE FUNCTION page of the PostgreSQL manual.
Unofficial utilities in the PLV8 ecosystem.
plv8ify
bundles typescript/javascript input and writes SQL file(s) containing
Postgres functions using PLV8.
For more information see the project readme.
equinox
allows for database level unit testing via PLV8.
See https://github.com/JerrySievert/equinox for more information.