How to perform action after SQL statement

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

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 particular PostgreSQL statement, include key of the record 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 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 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

If you want to perform queries that will generate the same event then use PQbackendPID function to get backend PID and compare it with PID received alongside the event.

You can disable 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

Milosz Galazka's Picture

About Milosz Galazka

Milosz is a Linux Foundation Certified Engineer working for a successful Polish company as a system administrator and a long time supporter of Free Software Foundation and Debian operating system. He is also open for new opportunities and challenges.

Gdansk, Poland https://sleeplessbeastie.eu