Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Notify Events From PostgreSQL to External Listeners

DZone's Guide to

Notify Events From PostgreSQL to External Listeners

What happens when you need to call external programs from a PostgreSQL database? Read on for a solution.

· Database Zone
Free Resource

Download the Guide to Open Source Database Selection: MySQL vs. MariaDB and see how the side-by-side comparison of must-have features will ease the journey. Brought to you in partnership with MariaDB.

Sometimes, we need to call external programs from our PostgreSQL database. We can send sockets from SQL statements. I've written about it. The problem with this approach is if the user rolls back the transaction. The socket has been already emitted. That's a problem (or not, depending on our application). Also, nobody guarantees that the process behind the socket server has access to the data of the transaction. If we're very fast, maybe the transaction isn't committed yet. We can use a sleep function, but sleep functions are always a bad idea. PostgreSQL gives us another tool to decouple processes: LISTEN and NOTIFY.

Let me show you and example. First we create a table:

CREATE TABLE PUBLIC.TBLEXAMPLE
(
    KEY1 CHARACTER VARYING(10) NOT NULL,
    KEY2 CHARACTER VARYING(14) NOT NULL,

    VALUE1 CHARACTER VARYING(20),
    VALUE2 CHARACTER VARYING(20) NOT NULL,

    CONSTRAINT TBLEXAMPLE_PKEY PRIMARY KEY (KEY1, KEY2)
)


Now we add an "after insert" trigger to our table:

CREATE TRIGGER TBLEXAMPLE_AFTER
AFTER INSERT
ON PUBLIC.TBLEXAMPLE
FOR EACH ROW
EXECUTE PROCEDURE PUBLIC.NOTIFY();


And now, within the trigger function, we send a notify event ("myEvent" in this case) with the row information. We need to send plain text in the notify event so we'll use JSON to encode our row data.

CREATE OR REPLACE FUNCTION PUBLIC.NOTIFY() RETURNS trigger AS
$BODY$
BEGIN
    PERFORM pg_notify('myEvent', row_to_json(NEW)::text);
    RETURN new;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;


Now we're going to build a server side example that connects to our PostgreSQL database and listen to the event. In this case, we're going to use Node.js to build the prototype. This example also will enqueue events into a gearman server.

var pg = require('pg'),
    gearmanode = require('gearmanode'),
    gearmanClient,
    conString = 'tcp://username:password@localhost:5432/gonzalo',
    pgClient;

gearmanode.Client.logger.transports.console.level = 'error';

gearmanClient = gearmanode.client();

console.log('LISTEN myEvent');
pgClient = new pg.Client(conString);
pgClient.connect();
pgClient.query('LISTEN myEvent');
pgClient.on('notification', function (data) {
    console.log("\033[34m" + new Date + '-\033[0m payload', data.payload);
    gearmanClient.submitJob('sms.sender.one', data.payload);
});


And that's all. Now we only need to perform an INSERT statement in our table. This process will trigger our event and our Node.js will enqueue the process into a gearman queue.

INSERT INTO PUBLIC.TBLEXAMPLE(KEY1, KEY2, VALUE1, VALUE2) VALUES ('k1', 'k2', 'v1', 'v2');


It's good to note  that if our insert statement is inside a transaction and we roll it back, notify won't send any message.

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.

Topics:
server ,postgresql ,insert ,trigger ,gearman ,sql ,database ,external ,event ,transaction

Published at DZone with permission of Gonzalo Ayuso, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}