Waiting for 9.0 – NOTIFY/LISTEN

note This article available in Russian.

People that are carefully watching the novelties in the world of PostgreSQL, not by hearsay familiar with blog of Hubert ‘depesz’ Lubaczewski. A series of his posts «Waiting for X.X» – a real fount of useful information.

He doesn’t forget the upcoming release either. There are already 34 posts from the series “Waiting for 9.0” on his blog. It would seem there is no chance to keep up with him. But looking through the release notes once again, I discovered a valuable innovation is overlooked. Namely, a new LISTEN/NOTIFY implementation.

Let’s start with dry facts.

Internal implementation change

At the moment (versions 8.x and below) mechanism uses a pg_listener system table as a storage. It contains all the listeners waiting for any notices. If necessary, the table is scanned and updated.

In the new version this will be replaced with an in-memory queue. First, it will give a huge increase in speed. And secondly, this implementation is also more compatible with Hot Standby usage. It should be noted however, that currently there is not yet any facility for HS slaves to receive notifications generated on the master, but implementation is planned for the future.


Finally, the developers added a second parameter for the NOTIFY command , the so-called «payload». Plans for the introduction of which were before the creation of the earth’s firmament.

This extra parameter represents a simple string literal up to 8000 characters. For everyday needs, I think, it’s enough. In the case of large data is recommended to keep them in the table and send notification with the record identifier.

Highlights summary

  • If a NOTIFY is executed within a transaction, a notification is not delivered until the transaction is committed.
  • If the listening session receives a notification signal during the transaction, notification itself will be delivered to the client only upon completion of the transaction, regardless of the result of the transaction itself (COMMIT or ROLLBACK).
  • If notifications are duplicated (the same channel name and payload), the server can fold several notifications into one.
  • Notifications from different transactions will be delivered “as is”, even in the case of duplication.
  • Notices will be delivered in the order in which they were sent. In the case of several transactions notifications will be delivered in the order in which the transactions committed.
  • In cases where it is impossible to provide the channel name or the payload, it’s more convenient to use the pg_notify(text, text) function, e.g.
    SELECT pg_notify(current_user, 'pay' || 'load');
  • Queue of notifications is limited to 8GB. If this queue becomes full (which is virtually impossible), transactions calling NOTIFY will fail at commit.

Some facts

As I said earlier, adding a new parameter was included in the TODO-list initially. Apparently the developers realized that in its present form, this functionality does not claim the laurels. However, the amount of work required for implementation, frightened (I suppose).

And on Nov. 11, 2009 Joachim Wieland, presented to the public patch with a new implementation mechanism for notifications. In this first edition the size of payload parameter was limited to 128 characters, that upset many of us.

There were letters with begging to increase the length of the additional parameter. And the citadel fell. 🙂 Size of 8000 characters, which we have now dictated only by internal constraints.

Global issues were discussed. Community revives in a few days later, when Joachim was working on the details. A simple question “What should we do if queue is full” caused a storm of emotions. Considering that under such conditions the situation it is likely not show itself ever. After all, this requires that the server has accumulated no fewer than 2,147,483,647 notification (now less because of the restrictions imposed in 8GB).

Those who wish to enjoy the holy war logs are welcome. 🙂

Who needs it?

This question each must answer for himself. The presence of an additional parameter opens up new horizons. If up to this point the client has received a formal news of the fact of change, he now has the opportunity to learn about the essence of what happened without having to perform additional queries on the server.

And you need it,% username?


One thought on “Waiting for 9.0 – NOTIFY/LISTEN

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s