Tutorial: Learn how to listen for table changes in Postgres now! 🤔

The PostgreSQL (Postgres) database is the first relational database change notification example that we’ll examine and in this tutorial I’ve included the instructions required to configure a trigger in PostgreSQL using the pg_notify function along with code to process the change events in the Groovy scripting language.

The Postgres API includes the PGNotificationListener interface which must be implemented and then registered with the database connection.

Note that there are two implementations of this available: the Postgres [default] JDBC driver and the Impossibl JDBC driver implementation.

In this case we do not want to use the Postgres driver, as that implementation will poll the database for changes.

Instead we will rely on the Impossibl driver implementation, which delivers event-driven asynchronous notifications.

How to execute event-driven change notifications in the Postgres Database

In the following we reference an example table. The trigger and function script must be executed inside Postgres as a precondition to running the Groovy script.

The notify_change Postgres trigger function will send PostgreSQL notify events to any registered listeners that are listening on the examplechannel channel — pay particular attention to the warning below, as channel names are case-sensitive.

Step One: Create a Postgres Trigger function

We need to create a trigger function that notifies on insert, update, or delete operations — an example regarding how this is done appears below — the function that returns a trigger appears on lines 1-17.

				
					CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
  BEGIN
    --
    -- WARNING: Case is VERY IMPORTANT here! If we use 'exampleChannel' PG converts this to
    --          examplechannel and no events will be received!!
    --
    --  UPDATE: [to be confirmed] Case can be handled in PostgreSQL by using double quotes.
    --
    --          In theory, if you had the following line as the listener, it would work in camelCase.
    --
    --          statement.execute('LISTEN "exampleChannel"');
    --
    --          The same applies to any identifier in Postgres.
    --
    PERFORM pg_notify('examplechannel', NEW.phrase);
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER table_change
  AFTER INSERT OR UPDATE OR DELETE ON example
  FOR EACH ROW EXECUTE PROCEDURE notify_change();

Step Two: Create a table change trigger

The second step takes place between lines 20-22 and involves creating a Postgres table change trigger such that insert, delete, and update operations on the example table result in the notify_change function being called.

				
					CREATE TRIGGER table_change
  AFTER INSERT OR UPDATE OR DELETE ON example
  FOR EACH ROW EXECUTE PROCEDURE notify_change();

Step Three: Create and configure an instance of the Postgres PGDataSource

We need to create an instance of the PostgreSQL PGDataSource and in the example below we set the host, port, database name, user and password properties.

				
					PGDataSource dataSource = new PGDataSource();
dataSource.setHost("0.0.0.0")
dataSource.setPort(5432)
dataSource.setDatabaseName("testdb")
dataSource.setUser("postgres")
dataSource.setPassword("password")

The dataSource is used to get the connection to the database on line #23, below.

Step Four: Implement The PostGreSQL PGNotificationListener Interface

Next, we implement the PGNotificationListener method and in this example we’ll print some information whenever this is called. The snippet below appears on lines #15 – #21 in the full example.

				
					final def pgNotificationListener = new PGNotificationListener () { 

  @Override
  public void notification(int processId, String channelName, String payload) {
    println("processId $processId, channelName: $channelName, payload: $payload")
  }
}

Step Five: Get A Connection To The Postgres Database

In step five we’ll get a connection to the PostgreSQL database from the dataSource — this appears on line #23.

				
					final def connection = (PGConnection) dataSource.getConnection()

Step Six: Register the pgNotificationListener with the Postgres database connection.

The connection will invoke methods on the pgNotificationListener so we register this via the addNotificationListener method below, and this is also shown on line #25.

				
					connection.addNotificationListener(pgNotificationListener)

Step Seven: Execute The LISTEN command on the examplechannel

This step takes place over lines #27 – #30 and requires us to create an instance of statement from the connection and then execute the LISTEN command on the examplechannel channel.

				
					final def statement = connection.createStatement()

statement.execute("LISTEN examplechannel")
statement.close()

The remaining code on lines #32 – #44 keep the program running so we can see the output when the NOTIFY command is executed.

Full Example

Below I’ve included the full example, which is written in the Groovy Scripting Language — you should be able to paste this into the GroovyConsole, make whatever changes are required to setup this example in your environment, and then run the script.

				
					@Grapes(
    @Grab(group='com.impossibl.pgjdbc-ng', module='pgjdbc-ng', version='0.8.4')
)
import com.impossibl.postgres.api.jdbc.PGConnection
import com.impossibl.postgres.api.jdbc.PGNotificationListener
import com.impossibl.postgres.jdbc.PGDataSource

PGDataSource dataSource = new PGDataSource();
dataSource.setHost("0.0.0.0")
dataSource.setPort(5432)
dataSource.setDatabaseName("testdb")
dataSource.setUser("postgres")
dataSource.setPassword("password")

final def pgNotificationListener = new PGNotificationListener () { 

  @Override
  public void notification(int processId, String channelName, String payload) {
    println("processId $processId, channelName: $channelName, payload: $payload")
  }
}

final def connection = (PGConnection) dataSource.getConnection()

connection.addNotificationListener(pgNotificationListener)

final def statement = connection.createStatement()

statement.execute("LISTEN examplechannel")
statement.close()

def time = 60 * 60 * 1000

println "Will sleep for $time milliseconds..."

try {
  Thread.sleep (time)
} catch (Throwable thrown) {
  thrown.printStackTrace (System.err)
} finally {
  connection.close ()
}

print "...done!"

We can see an example of this script being executed along with an explanation and the output which appears in the GroovyConsole in the following image.

Tutorial Conclusion

Learn more about database change notifications — a hidden gem supported by some relational databases as well as the video on Scaling Postgres Beyond PostgreSQL PgBouncer & Postgres Pgpool-II: Advanced Traffic Management.

This article is a content experiment based on keyword research using SEMrush.

Refer to the Java Programming Language, Groovy Scripting Language, and relational databases categories for other articles that include example code.

Finally, the COBOL Copybook Converter can help you read COBOL Copybooks into the R Project for Statistical Computing as properly structured data frames — this is an open-source data engineering project.

Frequently Asked Questions (FAQ)

Below I’ve included several frequently asked questions about Postgres as they pertain to this article.

What is a trigger in PostgreSQL?

A trigger in PostgreSQL is a database object that automatically executes or fires a specified procedural code in response to certain events on a particular table or view including INSERT, UPDATE, DELETE, and TRUNCATE operations. Triggers in Postgres can be used to enforce business rules, audit changes, and maintain complex integrity constraints that are difficult to define through standard constraints.

How to drop a trigger in Postgres?

To drop a trigger in PostgreSQL, use the following SQL command:

DROP TRIGGER trigger_name ON table_name;

Replace trigger_name with the name of the trigger you want to drop, and table_name with the name of the table from which you want to remove the trigger.

How to call trigger function in PostgreSQL?

In the PostgreSQL database, you don’t directly call a trigger function.

In order to call a trigger function, you link it to a specific event (such as INSERT, UPDATE, DELETE) on a table or view using the CREATE TRIGGER statement then you specify when it should activate (BEFORE, AFTER, or INSTEAD OF).

Finally the PostgreSQL database will automatically run the trigger function when the specified event happens.

ThosPFuller

I am a software engineer based in Northern Virginia (USA) and this website focuses on content engineering, content experiments, web development, search engine optimization and digital marketing ideas.

See Full Bio