pgjdbc-ng for Postgresql LISTEN and NOTIFY

Using Postgresql LISTEN pgjdbc-ng  to Improve Overhead

When using Postgresql LISTEN and NOTIFY, pgjdbc-ng is the perfect solution for notifications on table changes. I need to be notified when a table has a row inserted into it. Once notified, I can then read additional data to complete my processing. The initial idea was to poll the database every 10 seconds to see if a new row was inserted. This polling worked but with too much overhead. pgjdbc-ng was the answer.

Just What I Needed

Looking around I found that Postgresql has LISTEN and NOTIFY statements that support the event handling I am looking for. One issue was that the default Postgresql JDBC driver doesn’t block while waiting for the event to happen. Instead you need to continue to poll the database for the events. Better on the overhead since no query is actually executed but still was not optimal. I found that pgjdbc_ng is a re-written JDBC driver that does block and wait for an event to happen. Perfect solution! Also, as a side-note, the Postgresql C libraries work a bit differently.  Here is an example of the C implementation.

Whats needed to make this work using pgjdbc-ng

You will need a Table to monitor, a PSQL Function to send the notify event with a payload, a PSQL Trigger to call the above function on a row insert and Java code to listen for events.  

1 – Table to monitor

CREATE TABLE dm_queue
(
id integer,
domainid integer,
command character varying(1024)
);

2 – PSQL Function to send the notify event with a payload

CREATE OR REPLACE FUNCTION queue_event() RETURNS TRIGGER AS $$

DECLARE
data json;
notification json;

BEGIN

-- Convert the old or new row to JSON, based on the kind of action.
-- Action = DELETE? -> OLD row
-- Action = INSERT or UPDATE? -> NEW row
IF (TG_OP = 'DELETE') THEN
data = row_to_json(OLD);
ELSE
data = row_to_json(NEW);
END IF;

-- Contruct the notification as a JSON string.
notification = json_build_object(
'table',TG_TABLE_NAME,
'action', TG_OP,
'data', data);

-- Execute pg_notify(channel, notification)
PERFORM pg_notify('q_event',notification::text);

-- Result is ignored since this is an AFTER trigger
RETURN NULL;
END;

$$ LANGUAGE plpgsql;

3 – PSQL Trigger to call the above function on a row insert

CREATE TRIGGER queue_notify_event
AFTER INSERT ON dm_queue
FOR EACH ROW EXECUTE PROCEDURE queue_event();

4 – Java code to listen for events

Testing it out

Start the Java program running. Make sure to have the environment variables set so the program can connect to the database.

Next, go into pgAdmin or psql and run:

You should see the following output:
/channels/q_event {“table” : “dm_queue”, “action” : “INSERT”, “data” : {“domainid”:6,”id”:6,”command”:”here”}}

The payload is formatted into JSON by the Function and contains the full row that was inserted. Events can by issued on INSERT, UPDATE, and DELETE by just modifying the Trigger’s AFTER clause.
The Java program is in an infinite loop in order to test the notifies over and over.

Leave a Reply