Categories
SysOps

How to perform action after SQL statement

Sometimes I want to monitor and log particular PostgreSQL statements or execute other kinds of external action.
I am using the notify mechanism and slightly modified sample application found in the database documentation to achieve this.

In this example, I will print username and date added columns to standard error output after INSERT statement into users table.

This is simpler than it sounds.

Trigger

Create trigger to send a notification event after a particular PostgreSQL statement, include record key to keep payload minimal as it must be shorter than 8000 bytes.

CREATE FUNCTION users_function_trigger_notify() RETURNS trigger
    LANGUAGE plpgsql
    AS $
DECLARE
BEGIN
  PERFORM pg_notify('user_added',CAST(NEW.id AS text)); -- include table.id as _text_
  RETURN NEW;
END;
$;

Associate it with a specified table.

CREATE TRIGGER users_trigger_notify_after_insert AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE users_function_trigger_notify();

Application

Extend libpq testlibpq2.c application to listen for events and execute defined actions.

Modify it according to your needs.

#ifdef WIN32
#include <windows.h>
#endif
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/time.h>
#include <sys/types.h>
#include <postgresql/libpq-fe.h>
#include <strings.h>

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    PGnotify   *notify;

    PGresult   *int_res;
    char        int_query[60];
    char       *int_query_part = "select username, created from users where id="; // select required fields

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /*
     * Issue LISTEN command to enable notifications from the rule's NOTIFY.
     */
    res = PQexec(conn, "LISTEN new_user");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /*
     * should PQclear PGresult whenever it is no longer needed to avoid memory
     * leaks
     */
    PQclear(res);

    for (;;)
    {
        /*
         * Sleep until something happens on the connection.  We use select(2)
         * to wait for input, but you could also use poll() or similar
         * facilities.
         */
        int         sock;
        fd_set      input_mask;

        sock = PQsocket(conn);

        if (sock < 0)
            break;              /* shouldn't happen */

        FD_ZERO(&input_mask);
        FD_SET(sock, &input_mask);

        if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
        {
            fprintf(stderr, "select() failed: %s\n", strerror(errno));
            exit_nicely(conn);
        }

        /* Now check for input */
        PQconsumeInput(conn);
        while ((notify = PQnotifies(conn)) != NULL)
        {
            fprintf(stderr,
                    "ASYNC NOTIFY of '%s' received from backend PID %d payload %s\n",
                    notify->relname, notify->be_pid, notify->extra);
            PQfreemem(notify);

            // clear and prepare query
            strcpy(int_query,"");
            strcat(int_query,int_query_part);
            strcat(int_query,notify->extra);

            int_res=PQexec(conn,int_query);
            if (PQresultStatus(int_res) != PGRES_TUPLES_OK && PQntuples(int_res) == 1) 
            {
                fprintf(stderr, "SELECT command failed: %s", PQerrorMessage(conn));
                PQclear(int_res);
                exit_nicely(conn);
            }
            fprintf(stderr, "Added username %s at %s\n", PQgetvalue(int_res,0,0),PQgetvalue(int_res,0,1));
            
            /* perform external action here */

            PQclear(int_res);
        }
    }

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}

Look at the following diff output to spot the differences.

--- testlibpq2.c	2015-10-15 23:09:18.521790678 +0200
+++ main.c	2015-10-16 00:27:15.116980661 +0200
@@ -1,27 +1,3 @@
-/*
- * testlibpq2.c
- *      Test of the asynchronous notification interface
- *
- * Start this program, then from psql in another window do
- *   NOTIFY TBL2;
- * Repeat four times to get this program to exit.
- *
- * Or, if you want to get fancy, try this:
- * populate a database with the following commands
- * (provided in src/test/examples/testlibpq2.sql):
- *
- *   CREATE TABLE TBL1 (i int4);
- *
- *   CREATE TABLE TBL2 (i int4);
- *
- *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
- *     (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
- *
- * and do this four times:
- *
- *   INSERT INTO TBL1 VALUES (10);
- */
-
 #ifdef WIN32
 #include <windows.h>
 #endif
@@ -31,7 +7,8 @@
 #include <errno.h>
 #include <sys/time.h>
 #include <sys/types.h>
-#include "libpq-fe.h"
+#include <postgresql/libpq-fe.h>
+#include <strings.h>
 
 static void
 exit_nicely(PGconn *conn)
@@ -47,7 +24,10 @@
     PGconn     *conn;
     PGresult   *res;
     PGnotify   *notify;
-    int         nnotifies;
+
+    PGresult   *int_res;
+    char        int_query[60];
+    char       *int_query_part = "select username, created from users where id="; // select required fields
 
     /*
      * If the user supplies a parameter on the command line, use it as the
@@ -73,7 +53,7 @@
     /*
      * Issue LISTEN command to enable notifications from the rule's NOTIFY.
      */
-    res = PQexec(conn, "LISTEN TBL2");
+    res = PQexec(conn, "LISTEN new_user");
     if (PQresultStatus(res) != PGRES_COMMAND_OK)
     {
         fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
@@ -87,9 +67,7 @@
      */
     PQclear(res);
 
-    /* Quit after four notifies are received. */
-    nnotifies = 0;
-    while (nnotifies < 4)
+    for (;;)
     {
         /*
          * Sleep until something happens on the connection.  We use select(2)
@@ -118,15 +96,30 @@
         while ((notify = PQnotifies(conn)) != NULL)
         {
             fprintf(stderr,
-                    "ASYNC NOTIFY of '%s' received from backend PID %d\n",
-                    notify->relname, notify->be_pid);
+                    "ASYNC NOTIFY of '%s' received from backend PID %d payload %s\n",
+                    notify->relname, notify->be_pid, notify->extra);
             PQfreemem(notify);
-            nnotifies++;
+
+            // clear and prepare query
+            strcpy(int_query,"");
+            strcat(int_query,int_query_part);
+            strcat(int_query,notify->extra);
+
+            int_res=PQexec(conn,int_query);
+            if (PQresultStatus(int_res) != PGRES_TUPLES_OK && PQntuples(int_res) == 1) 
+            {
+                fprintf(stderr, "SELECT command failed: %s", PQerrorMessage(conn));
+                PQclear(int_res);
+                exit_nicely(conn);
+            }
+            fprintf(stderr, "Added username %s at %s\n", PQgetvalue(int_res,0,0),PQgetvalue(int_res,0,1));
+            
+            /* perform external action here */
+
+            PQclear(int_res);
         }
     }
 
-    fprintf(stderr, "Done.\n");
-
     /* close the connection to the database and cleanup */
     PQfinish(conn);

Compile the application.

$ gcc -lpq c.c -o application

Verify defined actions.

# ./application "host=localhost dbname=mydatabse user=myuser password=mypass"
ASYNC NOTIFY of 'new_user' received from backend PID 5855 payload 3418
Added username milosz at 2015-10-15 22:10:04
ASYNC NOTIFY of 'new_user' received from backend PID 5855 payload 3419
Added username michael at 2015-10-15 22:10:09

Additional notes

You can disable the trigger using the following query.

alter table users disable trigger users_trigger_notify_after_insert;

Download source code – pg_notify.tgz

References

PostgreSQL 9.4.5 Documentation – NOTIFY

PostgreSQL 9.4.5 Documentation – libpq – C Library

PostgreSQL 9.4.5 Documentation – libpq – C Library – Example Programs

PostgreSQL 9.4.5 Documentation – libpq – C Library – Connection Status Functions