Introduction to the Pg_notify method of Postgresql

Read this blog post today:

https://medium.com/namely-labs/syncing-cache-with-postgres-7a4d78cec022

Once again lamented the power of pg. The original PG can also be pg_notify this method, coupled with triggers, to actively tell the external data changes.

After reading the blog post, I used Golang to write one example, for reference:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	"github.com/lib/pq"
)

func main() {
	uri := "user=postgres password={YOUR_PASSWORD} host={YOUR_DB_HOST} port={YOUR_DB_PORT} dbname={YOUR_DB_NAME} sslmode=disable"
	db, err := sql.Open("postgres", uri)
	if err != nil {
		log.Fatal(err)
	}

	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}

	report := func(et pq.ListenerEventType, err error) {
		if err != nil {
			fmt.Println(err)
		}
	}

	listener := pq.NewListener(uri, 10*time.Second, time.Minute, report)
	err = listener.Listen("hello")
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("-------start listen------------")
	for {
		n := <-listener.Notify
		switch n.Channel {
		case "hello":
			fmt.Println("get notify : ", n.Extra)
		}
	}
}

The idea of the code is very simple, is to open a connection through Golang, and then use listener to listen to a channel. Here is a channel called "Hello". The program prints out the information that is received in the channel.

Currently the Pg_notify method of PG, only accept the string, hope can be extended later, then do not have to do the data type conversion.

When the Golang program runs, how do you test the effect? The simplest way to do this is to use Pgadmin to connect to the database in our program, open a query window, and then enter the following command and execute:

Select  pg_notify (' Hello ', ' world ');

The first parameter of the Pg_notify method is the channel, and the second method is the content to be sent, which is very simple.

When executing this SQL, the following results are printed almost simultaneously in the Golang program:

Does it feel magical? Anyway I am, haha.

With this mechanism of PG, we can let the database "proactively" tell us what data has been updated so that other actions can be triggered.

However, this mechanism is similar to the function of message middleware, but it is not, it will not help you to store messages, and will not persist. When PG sends out the message, it does not control whether it is being monitored. In other words, if the listener is started before the message is sent, it will not get the previous message. This is to be noted. If you do not mind this feature, it is recommended to open a few more listeners, or other methods to implement the real message middleware functionality.

Introduction to the Pg_notify method of Postgresql