Hidden Gems: Event-Driven Change Notifications in Relational Databases

Introduction

Wouldn’t it be great if we could receive event-driven change notifications (EDCN) when data changes directly from the database without having to poll for updates?

This feature is, in fact, available in some relational databases, but not all, as it’s non-standard functionality and not part of any SQL specification.

In the three examples covered in this article, this functionality is expressed via the implementation of an interface that is then registered with the JDBC driver directly. This opens the door to a myriad of potential use cases that can be expressed without the need to poll and which do not require the developer to write infrastructure code to deal with changes in data and notifying interested parties. Instead, we can interface with the driver directly and listen for changes and, when they occur, execute whatever workflow we have in an event-driven fashion. A few examples where this could be helpful include:

There are, of course, some consequences when relying on this functionality. The most obvious implication is that it’s a non-standard feature that ties the application directly to the database.

I was speaking with Michael Dürgner on LinkedIn about an example implementation as it pertains to PostgreSQL, and he commented that:

“[W]hile it’s definitely a great way to do this, one of the big drawbacks is that you move application logic into the RDBMS. Not saying you shouldn’t do it but make sure that you have people with deep understanding of the RDBMS you use on board since it’ll be rather unlikely your average software will be able to trouble shoot. Another huge challenge with this approach is continuous delivery since your RDBMS needs to be deeply integrated with your delivery pipeline.”

I agree with Michael’s position, and keeping business logic out of the database tends to be a good practice.

Projects that rely on object-relational mapping (ORM) tools such as the Java Persistence API (JPA) to generate the database schema directly from one or more object models immediately lose portability and simplicity when developers are required to add logic in the database tier which probably belongs in the application itself. If developers are not careful, they’ll end up having to use the same database for testing as used in production and this could easily lead to pain and regret.

I proffer the following question to any engineer considering using EDCNs via the JDBC driver: can the application still function as intended without the inclusion of whatever it is that you’re building that relies on this functionality? If the answer is “yes” then what you’re doing is likely fine; on the contrary, if the answer is “no”, then this is a strike against using EDCNs and alternatives may need to be considered.

Finally, this feature on its own is not a substitute for well-engineered message-oriented middleware (MOM), which typically provides out-of-the-box solutions for guaranteed delivery, message persistence, at-least-once/exactly-once delivery, delivery via queues and topics, strategies for flow control (see also: backpressure), and addresses fault tolerance and scalability concerns. The presence of these requirements could be a strong indicator that an approach relying on EDCNs needs to be reconsidered.

Below we explore this functionality as it exists in the PostgreSQLOracle, and H2 databases; we also include some general comments on MySQL and its fork, MariaDB.

Throughout this article, we rely on Java 13.0.2 and Groovy. 3.0.4 and include links to the various scripts on GitHub which contain extra notes pertaining to how to set up the required dependencies and any other preconditions necessary to run the examples.


PostgreSQL

The PostgreSQL (Postgres) database is the first example we’ll examine.

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. 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 implementation, which delivers true event-driven notifications.

I had the opportunity to speak with Erik Brandsberg, CTO of Heimdall Data about this, and Erik remarked that:

“The PG notify interface is one of the hidden gems in PG vs. other databases. We can use it to provide cache invalidation messages between proxies instead of using a distinct pub/sub interface such as on Redis.”

Heimdall Data delivers a sophisticated caching solution for applications that use Amazon Relational Database Service (Amazon RDS) and other databases and this is one real-world use case that demonstrates how important this functionality can be.

In the following example, the trigger and function script must be executed inside Postgres as a precondition to running the Groovy script. The notify_change function will send 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.

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();
view raw gistfile1.sql hosted with ❤ by GitHub

working example implementation of the com.impossibl.postgres.api.jdbc. PGNotificationListener using the PostgreSQL database is included next. The PGNotificationListener interface requires that the developer implement just one method:

void notification(int processId, String channelName, String payload)

We can see this on line #18 below.

@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.

Image for post
An explanation of the PostgreSQL example running in the Groovy Console.

The next example we’ll cover includes event-driven change notification functionality as it applies to the Oracle Database.


Oracle

The next example we’ll cover in this article will focus on the Oracle Database (Oracle). Below we detail the steps required to configure event-driven change notifications via the JDBC driver along with preconditions required to run the example.

The following two gists are required preconditions for this example. It is instructive to note that Docker was running on another machine which, in this case, uses the Ubuntu operating system. See the warning regarding running Oracle in Docker locally in the DatabaseChangeListenerInOracleDatabaseExample.groovy script for complete details.

#
# In this example Docker is running on another machine so assume that I've ssh'd into that box and
# am running the following on the remote machine.
#
docker run -d -p 1521:1521 oracleinanutshell/oracle-xe-11g
docker exec -it [container id] /bin/sh
su
#
# Username: system, password: oracle
#
/u01/app/oracle/product/11.2.0/xe/bin/sqlplus

In SQL*Plus, we can now run the following configuration script. Keep in mind that once the example table has been created, see line #8, the Groovy script in the next section can be started and any insert, update, or delete operations on the target table will result in an event being sent to the Groovy script and then printed to the console output.

This is required otherwise notifications won't be sent to the JDBC driver.
grant change notification to system;
commit;
CREATE TABLE example(
example_id NUMBER(10) PRIMARY KEY,
phrase VARCHAR2(120) NOT NULL
);
commit;
insert into example values (1, 'one');
insert into example values (2, 'two');
insert into example values (3, 'three');
insert into example values (4, 'four');
insert into example values (5, 'five');
commit;
Then once the DatabaseChangeListenerInOracleDatabaseExample.groovy is running
execute the following and an update should appear in the Groovy console:
update example set phrase = 'one / 1' where example_id = 1;

Here we have an example of the complete DatabaseChangeListenerInOracleDatabaseExample.groovy script. Note that the developer must implement one method:

void onDatabaseChangeNotification(DatabaseChangeEvent databaseChangeEvent)

We can see this implementation on line #55 below.

@GrabConfig(systemClassLoader=true)
//
// https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/index.html?oracle/jdbc/dcn/DatabaseChangeRegistration.html
//
// https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc6
@Grapes(
@Grab(group='com.oracle.database.jdbc', module='ojdbc6', version='11.2.0.4')
)
import oracle.jdbc.dcn.DatabaseChangeListener
import oracle.jdbc.dcn.DatabaseChangeEvent
import oracle.jdbc.driver.OracleConnection
import oracle.jdbc.dcn.DatabaseChangeRegistration
import oracle.jdbc.OracleStatement
import java.sql.DriverManager
import java.util.Properties
//
// Note that the thin driver supports this example.
//
//
// SEE THE WARNING BELOW ABOUT RUNNING THIS SCRIPT ON LOCALHOST WITH ORACLE DB IN DOCKER, ALSO ON LOCALHOST.
//
final def connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.232:1521:xe", "system", "oracle")
def databaseProperties = new Properties ()
/*
* [6] When the notification type is OCN, any DML transaction that changes one or more registered objects generates
* one notification for each object when it commits.
*
* When the notification type is QRCN, any DML transaction that changes the result of one or more registered
* queries generates a notification when it commits. The notification includes the query IDs of the queries whose
* results changed.
*
* For either notification type, the notification includes:
*
* Name of each changed table
*
* Operation type (INSERT, UPDATE, or DELETE)
*
* ROWID of each changed row, if the registration was created with the ROWID option and the number of modified rows
* was not too large. For more information, see ROWID Option."
*/
databaseProperties.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true")
databaseProperties.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION, "true")
final def databaseChangeRegistration = connection.registerDatabaseChangeNotification(databaseProperties)
public class ExampleDatabaseChangeListener implements DatabaseChangeListener {
@Override
public void onDatabaseChangeNotification(DatabaseChangeEvent databaseChangeEvent) {
println ("***** databaseChangeEvent: $databaseChangeEvent")
println ("***** databaseChangeEvent.source: ${databaseChangeEvent.source}")
println ("***** databaseChangeEvent.queryChangeDescription: ${databaseChangeEvent.queryChangeDescription}")
println ("***** databaseChangeEvent.tableChangeDescription: ${databaseChangeEvent.tableChangeDescription.each {println '\n – nextTableChangeDescription: $it' } }")
}
}
databaseChangeRegistration.addListener(new ExampleDatabaseChangeListener ())
final def statement = connection.createStatement()
statement.setDatabaseChangeRegistration(databaseChangeRegistration)
try {
resultSet = statement.executeQuery("select * from example")
while (resultSet.next())
{} // println "resultSet.phrase: ${resultSet.getString('phrase')}"
} catch (Throwable thrown) {
thrown.printStackTrace (System.err)
}
println "databaseChangeRegistration.userName: ${databaseChangeRegistration.userName}"
databaseChangeRegistration.tables.each {
println "tables: $it"
}
final def time = 60 * 60 * 1000
println "Will sleep for $time milliseconds…"
try {
Thread.sleep (time)
} catch (Throwable thrown) {
thrown.printStackTrace (System.err)
} finally {
statement.close ()
connection.close ()
}
println "…done!"
/* WARNING: I'm copy-pasting the below message because this is very important when running Oracle in Docker and then
* running this script on localhost. This caused me a few hours of time trying to figure out why the
* notification wasn't being received and ONLY APPLIES IF YOU'RE RUNNING DOCKER ON THE SAME MACHINE AS THIS
* SCRIPT IS BEING EXECUTED ON! In fact, I'm not bothering with this at the moment and am running Docker with
* Oracle on another machine entirely.
*
* Note also that I've not been able to get this running ON THE SAME MACHINE using:
*
* docker run -d -p 1521:1521 -p [47632:47632] oracleinanutshell/oracle-xe-11g
*
* FROM:
*
* https://stackoverflow.com/questions/26003506/databasechangeregistration-in-remote-server
*
* "You can check active listeners in the Oracle database running the following query:
*
* Select * FROM USER_CHANGE_NOTIFICATION_REGS
* I the query does not return any rows probably the database server can't access the jdbc driver listener port.
*
* By default the Oracle JDBC driver listens at port 47632 for notifications. You will need to ensure that it is possible to connect to that port from the database server. You may need to add a rule in the firewall to accept incoming requests to that port.
*
* This port can be changed with the NTF_LOCAL_TCP_PORT option:
*
* prop.setProperty(OracleConnection.NTF_LOCAL_TCP_PORT, "15000");"
*
*/

The following image goes into deeper detail regarding what each step is doing, along with some notes explaining the output.

An example of the DatabaseChangeListenerInOracleDatabaseExample groovy script running via the GroovyConsole.
A deeper explanation regarding what the example script, as it pertains to the Oracle database, is doing, including notes explaining the output.

Lastly, the following image demonstrates that when we perform five inserts in a row and then commit the changes, only a single event is emitted, which includes these five inserts. Events are only ever emitted whenever commit has returned successfully.

Image for post
Five inserts are executed in SQL*Plus followed by a single commit and we can see that this event includes these five insert operations.

The final example we’ll cover in this article includes the H2 database.


H2 Database

The H2 database is an open-source, lightweight, and very powerful relational database written entirely in Java. It supports a long list of features and ships as a single 2.2mb jar file. H2 is frequently used when testing Java applications and works well as an embedded database and can be used with object-relational mapping tools such as the Java Persistence API (JPA). H2 is also embedded in the JBoss Wildfly application server (JBoss) and has been in use in JBoss as an embedded database for a very long time.

H2 delivers event notifications via the org.h2.api.DatabaseEventListener interface. The DatabaseEventListener interface offers limited functionality when compared with the Postgres and Oracle listener specifications covered earlier. The methods required to implement the interface are:

void closingDatabase ()
void exceptionThrown(SQLException sqlException, String sql)
void init (String url)
void opened ()
void setProgress (String state, String name, int x, int max)

A working example implementation of the org.h2.api.DatabaseEventListener using the H2 database can be found on GitHub and is also included in the gist below followed by an image with pointers explaining how this works.

In this example, H2 is running in embedded mode — that is, H2 is running entirely in memory in the same virtual machine that the Groovy script is running in.

@GrabConfig(systemClassLoader=true)
@Grapes(
@Grab(group="com.h2database", module="h2", version="1.4.200")
)
import org.h2.api.DatabaseEventListener
import java.sql.SQLException
import java.sql.DriverManager
public class ExampleDatabaseEventListener implements DatabaseEventListener {
public void closingDatabase () {
println "closingDatabase: method invoked."
}
public void exceptionThrown (SQLException sqle, String sql) {
println "exceptionThrown: method invoked; sqle: $sqle, sql: $sql"
}
public void init (String url) {
println "init: method invoked; url: $url"
}
public void opened () {
println "opened: method invoked."
}
public void setProgress (int state, String name, int x, int max) {
println "setProgress: method invoked; state: $state, name: $name, x: $x, max: $max"
}
}
//
// Note the event listener has been added as a parameter in the connection URL below.
//
def cxn = DriverManager.getConnection("jdbc:h2:mem:EventListenerInH2DatabaseExampleDB;DB_CLOSE_DELAY=-1;DATABASE_EVENT_LISTENER='ExampleDatabaseEventListener';")
def stm = cxn.createStatement()
def resultSet = stm.executeQuery("SELECT 1+1")
if (resultSet.next()) {
println("next: ${resultSet.getInt(1)}")
}
cxn.close ()
println "…Done!"

In the following image, we can see an example of this script being executed, along with the output in the GroovyConsole.

Image for post
An example of the H2 DatabaseEventListener running in the Groovy Console including output.

It’s somewhat disappointing that the H2 DatabaseEventListener doesn’t offer similar functionality to that found in the PostgreSQL interface. As a result, I submitted a new feature request on the H2 Database repository on GitHub and may try to implement this myself, time permitting.


MySQL/MariaDB

Event-driven change notifications via the JDBC driver do not appear to be supported by either MySQL or MariaDB databases and so engineers will have to consider alternative solutions if this functionality is required.

We will not cover triggers and user-defined functions (UDF) as they pertain to MySQL and MariaDB to call a web service endpoint, which is one such alternative. Brief research on this subject suggests that triggers and UDFs can be used to accomplish this; however, they come with potentially significant security and performance implications that must be considered when taking this approach.

If you’ve used triggers and UDFs or some other solution to accomplish this within MySQL and/or MariaDB please feel free to elaborate on what you needed to do, the approach you took, and how well it worked out in the comments. Finally, If there’s a better solution available in MySQL and MariaDB, please explain further.


Conclusion

Have you used event-driven change notifications in relational databases in a project you were on? If so, I encourage you to discuss any of the following in the comments:

  • What was it needed for?
  • What database was used?
  • What language was the solution written in?
  • Can you do it with Microsoft SQL Server or another database not covered here?
  • Were any problems encountered, and how were they addressed?
  • Was this option explored and determined to not be an appropriate solution for the problem at hand?
  • Were you forced to use an alternative and, if so, what did the implementation look like and why?
  • Any other thoughts you might have, including any questions I might have left out here.

While EDCNs offer powerful functionality in supported relational databases, we hope that, in addition to providing some working examples, it has also been made clear that there’s a cost associated with relying on this feature and that you must give due consideration before using it.

Happy coding!

%d bloggers like this: