PG 表数据更新通知

PG 表数据更新通知

验证目的

当被依赖的数据发生更新时，通知相关引用程序，及时同步变化了的数据，以达到引用程序所缓存的数据与对应的数据库中被引用表数据一致效果。

本次仅验证数据插入操作，其它DML操作自行扩展。

---

准备过程

表模型

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);

c source code

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/time.h>
#include "libpq-fe.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;
    int nnotifies;

    /*
     * 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 TBL2");
    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);

    /* Quit after four notifies are received. */
    nnotifies = 0;
    while (nnotifies < 4)
    {
        /*
         * 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\n", notify->relname, notify->be_pid);
            PQfreemem(notify);
            nnotifies++;
        }
    }

    fprintf(stderr, "Done.\n");

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

    return 0;
}

编译代码

[root@cfg3 pldebugger]# gcc pgntf.c -L/usr/pgclient/lib/ -lpq -o pgTabChgNtf
[root@cfg3 pldebugger]#
[root@cfg3 pldebugger]#

[root@cfg3 pldebugger]# ls -l pgTabChgNtf
-rwxr-xr-x. 1 root root 13360 Apr 1 13:29 pgTabChgNtf

验证过程

启动程序

[root@cfg3 pldebugger]# ./pgTabChgNtf  "user=mypg port=5435 dbname=mydb password=123456Aa"

另启psql会话

[postgres@cfg3 ~]$ psql -p5435 mydb -Umypg
psql (13.2)
Type "help" for help.

mydb=# 
mydb=# 

插入数据操作

mydb=# 
mydb=# INSERT INTO TBL1 VALUES (1) ;
INSERT 0 1
mydb=# 
mydb=# INSERT INTO TBL1 VALUES (2) ;        
INSERT 0 1
mydb=# 
mydb=# INSERT INTO TBL1 VALUES (3);
INSERT 0 1
mydb=# INSERT INTO TBL1 VALUES (4) ;
INSERT 0 1
mydb=# INSERT INTO TBL1 VALUES (5);
INSERT 0 1
mydb=# 
mydb=# 

程序监控状态

ASYNC NOTIFY of 'tbl2' received from backend pid 72041
ASYNC NOTIFY of 'tbl2' received from backend pid 72041
ASYNC NOTIFY of 'tbl2' received from backend pid 72041
ASYNC NOTIFY of 'tbl2' received from backend pid 72041
Done.
[root@cfg3 pldebugger]# 
[root@cfg3 pldebugger]# 

---

验证结论

每当表tb1每次执行插入操作时，程序都成功接到表插入更新通知，若是数据缓存服务程序，就补充数据同步功能，始终与数据库对应表的数据保持一致。