Trigger arbitrary code from PostgreSQL

In this blog post we show how it is possible to run an arbitrary program, script, or execute arbitrary code in reaction to changes and generally events in a PostgreSQL database.

Triggers

Database management systems (DBMS) provide mechanisms for defining reactions to certain actions or, in other words, for defining that specific actions should trigger specific reactions. PostgreSQL, the DBMS used by CFEngine Enterprise, is no exception. These triggers can be used for ensuring consistency between tables when changes in one table should be reflected in another table, for recording information about actions, and many other things. PostgreSQL's Overview of Trigger Behavior describes the basics of triggers with the following sentences:

A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be attached to tables (partitioned or not), views, and foreign tables.

On tables and foreign tables, triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. UPDATE triggers can moreover be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement. Triggers can also fire for TRUNCATE statements. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.

On views, triggers can be defined to execute instead of INSERT, UPDATE, or DELETE operations. Such INSTEAD OF triggers are fired once for each row that needs to be modified in the view. …

Triggers are created by the CREATE TRIGGER SQL command and the PostgreSQL documentation specifies the syntax as follows:

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

As the two above snippets show, triggers can define reactions to a range of actions very specifically. However, the only type of reaction they can define is described on this line:

EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

with a bit more information following further on the page providing details for the specific parts of the CREATE TRIGGER SQL command, the function_name part in this case:

A user-supplied function that is declared as taking no arguments and returning type trigger, which is executed when the trigger fires.

In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.

Such function is defined by the CREATE FUNCTION SQL command and it can do many things and be written in various supported languages, but it is still quite limited. In CFEngine, we needed to react on some simple actions, but we needed a lot of logic in the reactions and we needed the reactions to use our code and very specific mechanisms.

Asynchronous Notifications

Fortunately, PostgreSQL provides Asynchronous Notifications. This mechanism can be used to notify listening clients about events, or, when looked at from a different angle, to broadcast a message to the listening clients. The usage is very simple. A channel name needs to be defined and then clients can subscribe to notifications from the particular channel by executing the LISTEN channel_name SQL command. The sender(s)/broadcaster(s) can then use the NOTIFY channel_name [, payload] command to send notifications to the channel, with an optional payload string providing more details about the event. There's no need (or even way, actually) to create or register the channel first.

It's easy to test the mechanism using the psql tool both sending and listening to the notifications in a channel:

We can see that whenever the tool sends a notification, it also immediately receives it, including the payload (if any) and the identifier of the sending process. Two psql processes can of course be used to simulate a more realistic situation where the senders and listeners of the notifications are separate processes and to check that the messages really go through the PostgreSQL DB and not just through some loopback in the psql process itself. The only tricky part of that is that the listening psql process only checks for new notifications when it executes an SQL command (communicates with the DB), it doesn't have any background watch thread.

Triggers and Asynchronous Notifications

The above two sections of this blog post describe two mechanisms for reacting to events in PostgreSQL – triggers and asynchronous notifications. Triggers can be used for automatic (implicit) reactions to various specific events happening on DB tables where the reactions are functions executed by the PostgreSQL DBMS in its limited environment. Asynchronous notifications can, on the other hand, be used for explicitly announcing some events or simply broadcasting messages and the clients listening to them are independent of the PostgreSQL DBMS, they only need a connection to it.

So how can arbitrary programs or scripts be run or arbitrary code be executed when something happens in a PostgreSQL database? One option is, of course, to add explicit NOTIFY SQL commands to the code that causes the changes in the database. And often this is the best approach because such code can decide whether to send the notification and when exactly to send it. But what if it is not desired or even possible to modify the code causing the changes in the database? The answer is, as it often is with modern technologies, integration and combination. Integration and combination of the two mechanisms described in this post – triggers and asynchronous notifications. Triggers cannot run arbitrary things, they can only execute functions in the DBMS. Clients listening to notifications can do whatever they want, but they need something or someone to send the notifications. Well, what if the functions that the triggers execute send the notifications? That way, every change in the database the trigger is hooked to will be announced with the respective notification.

As an example, let's take a look at what we are doing in CFEngine Enterprise (more details about this in an upcoming blog post):

CREATE TABLE IF NOT EXISTS __cmdb (
  hostkey     text        PRIMARY KEY,
  value       jsonb       NOT NULL,
  updated_at  timestamptz NOT NULL DEFAULT now(),
  epoch       bigint      NOT NULL DEFAULT 0
);

CREATE FUNCTION update_cmdb_data_file()
RETURNS TRIGGER AS $$
BEGIN
  EXECUTE 'NOTIFY cmdb_refresh, ''' || NEW.hostkey || '''';
  RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_cmdb_data_file
 AFTER INSERT OR UPDATE ON __cmdb
 FOR EACH ROW EXECUTE FUNCTION update_cmdb_data_file();

The above SQL code from our schema basically says: whenever a row in the __cmdb table is modified or a new row is added to the table, send a notification to the cmdb_refresh channel with the hostkey (first column) as the notification payload. This is done by a definition of a trigger AFTER INSERT OR UPDATE ON __cmdb that runs a trigger function executing a NOTIFY SQL command.

The clients listening on the channel then know that data for the particular host (identified by the hostkey) was modified or added and can do whatever they want in reaction. How to implement such clients, what they can do and what our real clients in CFEngine actually do will be the main topic of an upcoming blog post.

Notification-triggered code is not a trigger function

Writing trigger functions is not particularly easy. They use special languages and they have a quite limited execution environment and possibilities. Of course there are many PostgreSQL extensions adding many extra features and possibilities, but writing a bit of code in one's language of choice in a separate program that can do anything and run under an arbitrary user is definitely easier. And it can even run on a separate machine because all it needs is a connection to the PostgreSQL DB and an ability to run the LISTEN SQL command.

However, there is one crucial word in the description of what was introduced above – using triggers for asynchronous notifications in PostgreSQL – the word asynchronous. While trigger functions are run by the DBMS as part of the same transaction and (from Overview of Trigger Behavior again):

In all cases, a trigger is executed as part of the same transaction as the statement that triggered it, so if either the statement or the trigger causes an error, the effects of both will be rolled back.

for asynchronous notification this applies (from NOTIFY documentation):

NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed.

Although this may seem similar, there's a fundamental difference – while the code in a trigger function is executed as part of the transaction that triggered it, a transaction is completely independent (and unaware) of the code handling notifications that where sent as part of it.

In practice, if a trigger function is responsible for updating some related relation (table), there's a guarantee that such update will happen if and only if the change triggering the function happens and when the transaction containing that change ends, the trigger function code, and thus the update of the related relation, is done. If a trigger function sends a notification then the notification is sent if and only if the triggering changes happen and as part of the same transaction, but the clients handling the notification execute their reactions asynchronously and independently. There might be no such clients, one client or even multiple clients, remember the notifications are broadcasted to any number of clients that did LISTEN on the particular channel.

In the example case shown above, the code handling notifications in the cmdb_refresh channel cannot even assume that data for the host given by the payload (hostkey) is still in the table. There could have been another change in the DB after the notification was sent and before it was delivered that could have deleted the data. On the other hand, if there was a trigger ON DELETE on the same table sending a notification to the same channel, the two notifications, one triggered by the UPDATE or INSERT an the other one triggered by the DELETE would be guaranteed to be delivered in the same order as in which the changes happened.

Conclusions

Asynchronous notifications are great for many things, whether sent automatically (implicitly) by trigger functions or explicitly by the code making changes in a database. They provide a powerful mechanism for reacting to changes happening in a DB while giving an option to implement those reactions in an arbitrary language and execute them in an arbitrary environment. However, the code handling them cannot make any assumptions about the state of the DB and thus this mechanism cannot be used for things like ensuring consistency between multiple relations or something similar.

In an upcoming blog post we will see how they are being used in CFEngine Enterprise to make it more event-driven and less resource hungry. Both in combination with triggers and with code explicitly sending notifications in certain situations.