POSTGRESQL FUNCTIONS AND TRIGGERS

PostgreSQL lets you create user-defined functions using the CREATE FUNCTION SQL statement. Functions are essentially how PostgreSQL can manage user-defined pieces of logic.

Functions can be written in various languages – the most common one is probably PL/pgSQL, which is what you use when you write “stored procedures”. You can also write them in other languages, like Python and Perl.

They can also be written in C. For this, the C code has to be compiled into a dynamically loadable shared library (*.so). PostgreSQL can be told that a function lives as a certain symbol name in a certain *.so file. This is somewhat similar to how modules in Apache or Nginx work.

Functions can be used as triggers, which is what we’re interested in.

TRIGGERS

Triggers are a form of event handlers – they are pieces of logic that can be executed when certain events happen to specified objects. Typically, the objects involved are tables, but they can also be views or foreign tables.

The events, not surprisingly, are:

• insert (rows)
• update (rows)
• delete (rows)
• truncate (table)

PostgreSQL triggers are versatile:

• They can be invoked once per row or once per statement. For example, if a statement updates 5 rows, the trigger can be invoked once for the statement or 5 times, one for each row.
• They can be invoked before or after the actual change happens.
• The “before” triggers have a change to modify the values or cancel the change.
• Triggers can be used to impose any arbitrary constraint on a table.

The most popular use of triggers is probably creating audit logs (or more specifically, change logs). You can read more about triggers here and here.

DYNAMICALLY LOADABLE MODULES IN GO

Starting with version 1.5, Go has the ability to create C-style shared libraries. Using this, you can export an arbitrary Go function that can be invoked by other language runtimes – like dlopen/dlysm in C, ctypes in Python or JNI in Java.

You can build a C-style shared library in Go like this:

go build -o myso.so -buildmode=c-shared myso.go

Here myso.go is a Go main package, which looks like this:

packagemainimport"C"//export MyNamefuncMyName(xint)int{return42+x}funcmain(){// empty}

Note the “decorator” comment just above the exported function. The import "C" statement is also required for the export to happen.

WRITING POSTGRESQL FUNCTIONS IN GO

With this feature, we can build a *.so file contains an exported method that can be invoked as a PostgreSQL function.

There are some conventions that must be adhered to when writing this function – they are detailed here.

Let’s start off by defining the “module”, and listing an exported function called mytrigger.

// file module.gopackagemain/*

#include "postgres.h"

#include "fmgr.h"

#cgo LDFLAGS: -Wl,--unresolved-symbols=ignore-all

#ifdef PG_MODULE_MAGIC

PG_MODULE_MAGIC;

#endif

PG_FUNCTION_INFO_V1(mytrigger);

*/import"C"funcmain(){}

Note the LDFLAGS declaration. This lets us build the so file without the linker complaining about unresolved symbols. For PostgreSQL, there are no libraries to link against, and the symbols that are needed by our shared library can be verified only when the so file is loaded by PostgreSQL.

Next, let’s flesh out the trigger function itself in another file mytrigger.go:

// file mytrigger.gopackagemain/*

#include "postgres.h"

#include "commands/trigger.h"

//...

*/import"C"import("fmt""unsafe")//export mytriggerfuncmytrigger(fcInfo*C.FunctionCallInfoData)C.Datum{trigdata:=(*C.TriggerData)(unsafe.Pointer(fcInfo.context))//...}

The signature of the exported Go function, mytrigger, is mandated by the PostgreSQL function manager convention. In case of triggers, this function is passed the row itself, which it can possibly modify (in case of “before” triggers), and return back.

For now, we’ll create a simple function that will be triggered after INSERTs and UPDATEs. It will not modify the data, and will return it back unchanged. Let’s also assume that the first column in the row will of type “text”, which we’ll read and print.

Now would be a good time to look at how the trigger would look like in C. Here is the example from the PostgreSQL docs.

Within the function, we want to first get the correct row data, since the function can be invoked via an INSERT or an UPDATE:

varrettuple*C.HeapTupleDataifC.trigger_fired_by_update(trigdata.tg_event)!=0{rettuple=(*C.HeapTupleData)(trigdata.tg_newtuple)}else{rettuple=(*C.HeapTupleData)(trigdata.tg_trigtuple)}

And then we’ll extract the first column data (indices start from 1), assuming it is a “text” data type (with no embedded NULs):

url:=C.GoString(C.getarg_text(trigdata,rettuple,1))

We’ll just print it out for now, rather than actually processing it:

C.elog_info(C.CString(fmt.Sprintf("got url=%s",url)))fmt.Println(url)

And finally return the original, unmodified data:

returnC.pointer_get_datum(rettuple)

The full file can be seen here. See below for the github repo link and build instructions.

RUNNING THE TRIGGER

To see the trigger in action, first let’s create a table:

$ sudo -u postgres psql -d test

psql (9.6.2)

Type "help" for help.

test=# CREATE TABLE urls ( url TEXT );

CREATE TABLE

test=#

And then our function (you’ll need the USAGE privilege on language C for this):

test=# CREATE FUNCTION mytrigger()

test-# RETURNS TRIGGER AS '/home/alice/ptgo/ptgo.so'

test-# LANGUAGE C;

CREATE FUNCTION

test=#

Next let’s create a trigger on INSERT and UPDATE on table urls, that invokes our function:

test=# CREATE TRIGGER trig_1

test-# AFTER INSERT OR UPDATE

test-# ON urls

test-# FOR EACH ROW

test-# EXECUTE PROCEDURE mytrigger();

CREATE TRIGGER

test=#

Now let’s insert a couple of rows. The “got url=” lines are printed by our function:

test=# INSERT INTO urls VALUES ('http://example.com/');

INFO:  got url=http://example.com/

INSERT 0 1

test=#

test=# INSERT INTO urls VALUES ('http://mydomain.com/');

INFO:  got url=http://mydomain.com/

INSERT 0 1

test=#

And when the rows are updated, the function receives the post-change values because it is an AFTER trigger:

test=# UPDATE urls SET url='http://www.test.com/';

INFO:  got url=http://www.test.com/

INFO:  got url=http://www.test.com/

UPDATE 2

test=#

And that’s it! We have our very own PostgreSQL trigger written in Go!

THE CODE

The entire code is available on GitHub here: github.com/rapidloop/ptgo. Feel free to fork it and modify it to implement your own triggers. It has been tested only on Linux. To get started, do:

git clone https://github.com/rapidloop/ptgo

cd ptgo

make

You might need to install the development package for Postgres first. For Debian-based systems, this can be done with:

sudo apt-get install postgresql-server-dev-9.6

NEW HERE?

OpsDash is a server monitoring, service monitoring, and database monitoring solution for monitoring MySQL, PostgreSQL, MongoDB, memcache, Redis, Apache, Nginx, Elasticsearch and more. It provides intelligent, customizable dashboards and spam-free alerting via email, HipChat, Slack, PagerDuty and Webhooks. Send in your custom metrics with StatsD and Graphite interfaces built into each agent.