March 10, 2010 at 14:17Waiting for 9.0: NOTIFY / LISTEN
People who are closely following the news in the PostgreSQL world are not familiar with Hubert 'depesz' Lyubashevsky's blog . And the cycles of his Waiting for XX posts are a real storehouse of useful information.
He did not forget about the upcoming release. His blog already has 34 posts from the Waiting for 9.0 series . It would seem that keeping up with a Polish brother is not possible. But once again, looking through the release notes , I found a valuable innovation, deprived of attention. Namely, a new implementation of the LISTEN / NOTIFY mechanism.
I'll start with the facts. And in conclusion, I will describe the seething life that accompanied the implementation of this functionality.
At the moment (in versions 8.x and below), the mechanism uses a system table
In the new version, all this will be implemented in the form of a queue located in RAM. Firstly, it will give a huge increase 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 to receive notifications from the master, but implementation is planned for the future.
Finally, the developers added a second parameter for the NOTIFY command, the so-called “payload” (payload). Plans for the implementation of which were before the creation of the earth's firmament.
This additional information is a regular string up to 8000 characters long. For everyday needs, I believe, enough with a head. In the case of big data, it is recommended to save them in a table, and in the notification to transmit the record identifier.
As I said, adding a new parameter to the NOTIFY command format was included in the TODO list initially. Apparently, the developers understood that in their current form this functionality does not claim to be laurels. However, the amount of work required for implementation was scary.
And on November 11, 2009, Joachim Wieland submitted to the public a patch with a new implementation of the notification mechanism. In this first edition, the size of the additional information (payload) was limited to 128 characters, which openly upset many.
The author received letters with open pleas for an increase in the length of the additional parameter. And the citadel fell. The size of 8000 characters, which we have now, is dictated only by internal restrictions.
The patch discussion thread counted a total of 63 letters. Global issues have been settled. The community revived a few days later when Joachim worked on the details. The simple question “What should be done when the queue is full” caused a storm of emotions. Despite the fact that the overflow situation itself is likely to never show itself. After all, for this it is necessary that no less than 2,147,483,647 notifications accumulate on the server (now it is already less due to the introduced 8GB limit).
Those wishing to enjoy the logs of the holy war of mercy, please archive .
Everyone should answer this question for himself. The presence of an additional parameter opens up new horizons. If until this moment the client received only formal news about the change, now he has the opportunity to learn about the essence of what happened without fulfilling additional requests on the server.
Do you need it,% username?
He did not forget about the upcoming release. His blog already has 34 posts from the Waiting for 9.0 series . It would seem that keeping up with a Polish brother is not possible. But once again, looking through the release notes , I found a valuable innovation, deprived of attention. Namely, a new implementation of the LISTEN / NOTIFY mechanism.
I'll start with the facts. And in conclusion, I will describe the seething life that accompanied the implementation of this functionality.
Replacing the internal implementation of NOTIFY / LISTEN
At the moment (in versions 8.x and below), the mechanism uses a system table
pg_listenerto store notifications. It contains all the "listeners" awaiting any notice. If necessary, the table is scanned and updated. In the new version, all this will be implemented in the form of a queue located in RAM. Firstly, it will give a huge increase 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 to receive notifications from the master, but implementation is planned for the future.
Payload
Finally, the developers added a second parameter for the NOTIFY command, the so-called “payload” (payload). Plans for the implementation of which were before the creation of the earth's firmament.
This additional information is a regular string up to 8000 characters long. For everyday needs, I believe, enough with a head. In the case of big data, it is recommended to save them in a table, and in the notification to transmit the record identifier.
Briefly about the main thing
- If NOTIFY is performed within a transaction, notifications are not sent until the transaction is completed (COMMIT).
- If the “listening” session receives a notification signal during the transaction, the 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 additional information), then the server can combine several notifications into one.
- Notifications from different transactions will be delivered “as is” without combining, even in the case of duplication.
- Notifications will be delivered in the order in which they were sent. In the case of transactions, notifications are delivered in order of completion of transactions.
- In cases where it is not possible to represent the channel name or information as a string, it is convenient to use the function
pg_notify(text, text). For instance,SELECT pg_notify(current_user, 'pay' || 'load'); - The notification queue is limited to 8GB. When filling out (which is almost impossible) the transaction that overflowed 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 initially. Apparently, the developers understood that in their current form this functionality does not claim to be laurels. However, the amount of work required for implementation was scary.
And on November 11, 2009, Joachim Wieland submitted to the public a patch with a new implementation of the notification mechanism. In this first edition, the size of the additional information (payload) was limited to 128 characters, which openly upset many.
The author received letters with open pleas for an increase in the length of the additional parameter. And the citadel fell. The size of 8000 characters, which we have now, is dictated only by internal restrictions.
The patch discussion thread counted a total of 63 letters. Global issues have been settled. The community revived a few days later when Joachim worked on the details. The simple question “What should be done when the queue is full” caused a storm of emotions. Despite the fact that the overflow situation itself is likely to never show itself. After all, for this it is necessary that no less than 2,147,483,647 notifications accumulate on the server (now it is already less due to the introduced 8GB limit).
Those wishing to enjoy the logs of the holy war of mercy, please archive .
Who needs this?
Everyone should answer this question for himself. The presence of an additional parameter opens up new horizons. If until this moment the client received only formal news about the change, now he has the opportunity to learn about the essence of what happened without fulfilling additional requests on the server.
Do you need it,% username?