Triggers in PostgreSQL are a simple yet powerful mechanism to react to changes happening in tables.
Read on to find out how to write PostgreSQL triggers in Go.
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 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.
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:
myso.go is a Go
main package, which looks like this:
Note the “decorator” comment just above the exported function. The
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
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
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:
And then we’ll extract the first column data (indices start from 1), assuming it is a “text” data type (with no embedded NULs):
We’ll just print it out for now, rather than actually processing it:
And finally return the original, unmodified data:
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:
And then our function (you’ll need the
USAGE privilege on language C
Next let’s create a trigger on INSERT and UPDATE on table
urls, that invokes
Now let’s insert a couple of rows. The “got url=” lines are printed by our function:
And when the rows are updated, the function receives the post-change values
because it is an
And that’s it! We have our very own PostgreSQL trigger written in Go!
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:
You might need to install the development package for Postgres first. For Debian-based systems, this can be done with:
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.