Waiting for 9.0: NOTIFY/LISTEN

People who are watching closely developments in the world of PostgreSQL, not by hearsay familiar with blog Hubert 'depesz' Lyubashevsky. And the cycles of his posts "Waiting for X. X" — a real fount of useful information.

He did not forget about the forthcoming release. On his blog already there are 34 of post cycle "Waiting for 9.0". It would seem that to keep up with the brother of a pole is not possible. But once again looking release notes, I discovered a valuable innovation, deprived of attention. Namely a new implementation of the LISTEN/NOTIFY mechanism.

Start with the dry facts. In conclusion, we describe the wildness of life that accompanied the implementation of this functionality.

replace the internal implementation of NOTIFY/LISTEN

At the moment (versions 8.x and below), the mechanism uses the system table pg_listener to store notifications. It includes all the "listeners" waiting for any notice. If necessary, alert the table is scanned and updated.

In the new version all this will be implemented in the form of queues located in memory. First, it will give huge gains in speed. And secondly, this implementation is compatible with the Hot Standby mechanism. It should be noted however, that at the moment there is no possibility for HS-slave'a to receive notifications from master a, but the implementation is planned for the future.

payload

Finally, the developers have added the second parameter for the NOTIFY command, the so-called "payload" (payload). Plans for the introduction of which was before the Foundation of the earth.

This additional information represents a string of up to 8000 characters. For everyday needs, I believe, will be enough. In the case of big data, it is recommended to save them in the table and in the notification to pass the entry identifier.

quick facts

the

the
• If a NOTIFY is executed within a transaction, notifications are not sent until until the transaction is completed (COMMIT).
the
• If the "listening" session receives a notification signal during a transaction, the notification will be delivered to the customer only after the transaction regardless of the result of the transaction (COMMIT or ROLLBACK).

If notifications are duplicated (same channel name and additional information), the server can merge multiple notifications into one. the

• Notifications from different transactions are delivered "as is" without unions, even in the case of duplication.
the
• Notifications will be delivered in the order in which it was sent. In the case of transaction notifications are delivered in the order of completion of transactions.
the
• In cases where it is impossible to imagine the name of the channel or information line, it is convenient to use the function pg_notify(text, text). For example,
  the SELECT pg_notify(current_user, 'pay' || 'load');
  
the
• the notification Queue is limited to 8Gb. When filling (which is practically impossible), a transaction that broke the queue will be rolled back.
  

How it was

As I said, adding a new parameter to the NOTIFY command format was included in the TODO-list originally. Apparently the developers realized that in its current form, this feature does not claim the laurels. However, the amount of work required to implement, scared.

And here on 11 November 2009 Joachim Veland (Joachim Wieland) introduced to the public a patch with a new implementation of the notification mechanism. In this first edition volume of additional information (the payload) was limited to 128 characters that many are frankly disappointed.

The author received letters with blatant pleas to increase the length of the additional parameter. And the citadel fell. Size to 8000 characters, which we have now dictated only by internal constraints.
The discussion thread patch counted a total of 63 letters. Global issues were settled. The community revived after a few days, when Joachim worked on the details. A simple question, "What should I do when the queue is overflow" caused a storm of emotions. Despite the fact that the overflow situation, it will probably not show itself ever. Because it requires the server to have accumulated no less 2,147,483,647 notifications (now less because of the restrictions in 8Gb).

Guests wishing to have a Holy war logs welcome to archive.

Who needs it?

This question everyone must answer for himself. The presence of additional parameters opens up new horizons. If up to this point, the client received a formal news of the change, now he has the opportunity to learn about the nature of the incident, without performing additional queries on the server.

And you need it, %username?

Article based on information from habrahabr.ru