Pending 9.0: NOTIFY / LISTEN
People who closely follow the news in the world of PostgreSQL , are not hearsay familiar with Hubert's blog 'depesz' Lyubashevsky. And the cycles of his posts "Waiting for XX" - a real storehouse of useful information.
He did not forget about the upcoming release. There are already 34 posts on the “Waiting for 9.0” cycle on his blog. It would seem that it is not possible to keep up with the Polish brother. But once again looking at the release notes , I found a valuable innovation, deprived of attention. Namely, the new implementation of the LISTEN / NOTIFY mechanism.
I'll start with the dry facts. And in conclusion I will describe the turbulence of life that accompanied the introduction of this functionality.
At the moment (in versions 8.x and below), the mechanism uses the
')
In the new version, all this will be implemented as a queue located in RAM. First, it will give a huge increase in speed. And secondly, this implementation is compatible with the mechanism of Hot Standby. It is worth noting, however, that at the moment there is no possibility for the HS-slave to receive notifications from the master, but the 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 introduction of which were before the creation of the earth's firmament.
This additional information is a regular line up to 8000 characters long. For everyday needs, I believe, enough with his head. In the case of big data, it is recommended to save them in a table, and in the notification to pass the record identifier.
As I said earlier, adding a new parameter to the NOTIFY command format was included in the TODO list initially. Apparently, the developers understood that in its current form, this functionality does not claim laurels. However, the amount of work required for the implementation was scary.
And on November 11, 2009, Joachim Wieland presented a patch to the public 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 upset many frankly.
The author received letters with undisguised pleas to increase the length of the additional parameter. And the citadel fell. The size of 8000 characters that we now have is dictated only by internal restrictions.
The patch discussion thread counted only 63 letters. Global issues have been settled. The community came alive 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. Given that the situation itself overflow, it is quite likely that it will never manifest itself. After all, this requires that no less than 2,147,483,647 notifications have accumulated on the server (now less due to the 8GB limit imposed).
Those who want to enjoy the logs of the holy war, you are welcome to the archive .
Everyone has to answer this question for himself. The presence of an additional parameter opens up new horizons. If, up to this point, the client has received only formal news about the change, then now he has the opportunity to learn about the essence of the incident without performing additional requests on the server.
Do you need it,% username?
He did not forget about the upcoming release. There are already 34 posts on the “Waiting for 9.0” cycle on his blog. It would seem that it is not possible to keep up with the Polish brother. But once again looking at the release notes , I found a valuable innovation, deprived of attention. Namely, the new implementation of the LISTEN / NOTIFY mechanism.
I'll start with the dry facts. And in conclusion I will describe the turbulence of life that accompanied the introduction of this functionality.
Replacing the internal implementation of NOTIFY / LISTEN
At the moment (in versions 8.x and below), the mechanism uses the
pg_listener system table for storing notifications. It contains all the "listeners" waiting for any notification. If necessary, the alert table is scanned and updated.')
In the new version, all this will be implemented as a queue located in RAM. First, it will give a huge increase in speed. And secondly, this implementation is compatible with the mechanism of Hot Standby. It is worth noting, however, that at the moment there is no possibility for the HS-slave to receive notifications from the master, but the 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 introduction of which were before the creation of the earth's firmament.
This additional information is a regular line up to 8000 characters long. For everyday needs, I believe, enough with his head. In the case of big data, it is recommended to save them in a table, and in the notification to pass the record identifier.
Briefly about the main thing
- If NOTIFY is executed within a transaction, no notifications are sent until the transaction is completed (COMMIT).
- If the “listening” session receives a notification signal during a transaction, the notification itself will be delivered to the client only after the 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 merging, 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 impossible to represent the channel name or information in a string, it is convenient to use the
pg_notify(text, text)function. For example,SELECT pg_notify(current_user, 'pay' || 'load'); - The notification queue is limited to 8GB. When filling (which is almost impossible), the transaction that overflowed the queue will be rolled back.
How it was
As I said earlier, adding a new parameter to the NOTIFY command format was included in the TODO list initially. Apparently, the developers understood that in its current form, this functionality does not claim laurels. However, the amount of work required for the implementation was scary.
And on November 11, 2009, Joachim Wieland presented a patch to the public 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 upset many frankly.
The author received letters with undisguised pleas to increase the length of the additional parameter. And the citadel fell. The size of 8000 characters that we now have is dictated only by internal restrictions.
The patch discussion thread counted only 63 letters. Global issues have been settled. The community came alive 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. Given that the situation itself overflow, it is quite likely that it will never manifest itself. After all, this requires that no less than 2,147,483,647 notifications have accumulated on the server (now less due to the 8GB limit imposed).
Those who want to enjoy the logs of the holy war, you are welcome to the archive .
Who needs it?
Everyone has to answer this question for himself. The presence of an additional parameter opens up new horizons. If, up to this point, the client has received only formal news about the change, then now he has the opportunity to learn about the essence of the incident without performing additional requests on the server.
Do you need it,% username?
Source: https://habr.com/ru/post/87002/
All Articles