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