Playing with notify command – Postgresql vol. 1

Comment

What notify command does

The notify command allows sending notification to specific channels on database system. It is a very simple way to perform communication between several process that needs access a single database.

Working together with triggers the developer can assure that any process knows that specific operations was successful.

A very simple “get started”

To this first step we can use a already existing database. After access yours database shell, run the follow commands:

postgres=# LISTEN test;
LISTEN

Lets get what happened here:

The command LISTEN registers on the current session a “listener” to our notifications. We that listener of “test”. If  there is a listener called “test” on this session nothing happens.

After that, is the moment that send our notification.

postgres=# NOTIFY test;
NOTIFY
Asynchronous notification "test" received from server process with PID 1234. #The PID number will be different on your system

Cool! We just send and receive our first notification direct from database! Look that server register that notification with the follow message:

Asynchronous notification "test" received from server process with PID 1234.

This is very useful.

Example: you can receive a notification on your email when some table on your database receive a new item, or when a existing item on table was updated. To perform that we just create a trigger that run under this events. This trigger, at its time, notifies the event.

Something more complex

Lets increase the complexity of our experiments:

  1. Lets create a database with only one table;
  2. Lets create a trigger that will run after insert on table;
  3. We will register the listener;
  4. The trigger will send the notifications;

The code bellow require more acknowledged about postgres; however it will be simple and easy to follow.

1 – Creating database:

createdb -U postgres -h localhost -W playground
psql playground

p.s.: you can use any name for your database. Choose yours ;-)

2 – Creating the table. We’ll called “table_a”:

playground=# create table table_a (username varchar(512), email varchar(512));

2.1 – Creating  stored procedure to notify the events:

playground=# create function notify_trigger() returns trigger as $$
playground$# declare
playground$# begin
playground$# execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP;
playground$# return new;
playground$# end;
playground$# $$ language plpgsql;
CREATE FUNCTION

One more time, we’ll get what happened.

1 – On first line we found the code:

playground=# create function notify_trigger() returns trigger as $$

This piece of code creates a function: notify_trigger().

The follow lines perform the variables declarations (if it needs) and indicates where the function’s body really begins.

2 – When it runs, the function will perform the command NOTIFY;

execute 'NOTIFY ' || TG_TABLE_NAME || '_' || TG_OP;

3 – Look the variables TG_TABLE_NAME e TG_OP – these are variables created automatically when database runs the function. The TG_TABLE_NAME stores the table’s name that receive the operation and TG_OP stores the operation’s name that shot the function. The result string will be “table_a_insert” or “table_a_update”. This will be the channel to our listener.

3 – Creating the triggers:

playground=# create trigger table_a_trigger before insert or update on table_a execute procedure notify_trigger();

4 – Lets register the listener (remember of 2.1.3 item above?) :

playground=# LISTEN table_a_insert;

Finally we make a test:

playground=# insert into table_a (username, email) values ('some_thing_cool', 'somethingcool@myemailserver.com');

The server returns:

INSERT 0 1
Asynchronous notification "table_a_insert" received from server process with PID 12345. #The PID number will be different on your system

We have a notification for each insert that run on table_a.

You can perform a test to verify if notify will be sent when a update runs on table_a. Remember that you should first declare the listener to get the update notifications.

This was a little introduction to notify on Postgres. On future post (vol. 2) we’ll see a script python to receive the database notifications.

References:

|