CYBERTEC PostgreSQL Logo

LISTEN / NOTIFY: Automatic client notification in PostgreSQL

09.2022 / Category: / Tags: |

What is LISTEN and NOTIFY?

LISTEN / NOTIFY is a feature that enables users to listen to what goes on in the database. It is one of the oldest functionalities in PostgreSQL and is still widely used. The main question is: What is the purpose of the asynchronous query interface (LISTEN / NOTIFY), and what is it good for? The basic idea is to avoid polling.

Often, code works like this:

If thousands of users keep constantly polling the database, a lot of load is caused for no benefit. Most polling requests won’t yield anything - there has to be a better way. LISTEN / NOTIFY is exactly that: A better way that helps us to avoid polling the database over and over again. Instead, we hook up to the database and wait until some relevant event wakes us up.

Set up PostgreSQL notifications

To make use of PostgreSQL notifications two commands are relevant: LISTEN and NOTIFY:

LISTEN will make sure that your database connection listens on a “channel”. Basically a channel is just a name. There is no need to create one - no need to ensure that it actually exists. We listen on this name and wait until a notification drops in.

Send a notification to waiting database connections: use the NOTIFY command:

All we need in order to send a notification is: a channel and an optional payload (that's a normal string which is transmitted to the receiver).

Let's try it out and see how it works:

LISTEN makes the backend aware of the fact that we want to know about messages coming via channel “x”. Usually an application listens to one channel. However, nothing stops us from issuing multiple LISTEN commands to listen to more than just one channel at a time. This is perfectly feasible and sometimes even highly desirable.

What happens when a notification is issued?

The notification will be delivered to all connections that issued a LISTEN command to attach to the same channel.

Use triggers with LISTEN / NOTIFY

Often we want to notify a client when a row is added to a table. To do that, use a trigger installed for a table. Here's an example which shows how this works:

Another way: pg_notify()

The code shows how to create a table and how to create the trigger to send out notifications. In my example, I used EXECUTE to run dynamic SQL. However, there is also a second method:

The pg_notify() function is a more elegant way to send out a message.

To test the function we issue an INSERT statement:

[sql gutter="false"]
INSERT INTO t_message (message) VALUES ('sample text');

The message will be sent:

[sql gutter="false"]
NOTICE:  sending message for INSERT, (1,'2022-07-13 16:18:24.709008+02','sample text')

The notification is NOT sent out immediately but only after the transaction is actually committed. Why is that an important detail? If the transaction was sent out immediately, the changes would not be visible to other transactions. Note that changes are only seen by your own transaction - unless you commit. The second transaction receives the message as soon as the first transaction ends successfully. Keep in mind: Notifications are also a transactional thing - they are only delivered on commit and not in case of a rollback.

Finally …

There is an application of LISTEN/NOTIFY here: /en/automatic-partition-creation-in-postgresql/

To learn more about how transactions work in PostgreSQL, see Laurenz Albe's post on Transaction Anomalies with SELECT for UPDATE, or have a look at his post about WITH HOLD transactions and cursors. Our transaction archive holds all our posts on transactions.

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram