A business meeting


Amazing story!
That is how the developer suffers from his superiors.


To work or not to work?

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?

PostgreSQL vs MySQL: Russian reality

note This article available in Russian.

This post is some kind of statistic about campaign had place on the Russian IT-community site Habrahabr.ru. On behalf of the MicroOLAP company we gave away free personal licenses for Database Designers for PostgreSQL and MySQL there.


I will not discover the exact licenses count, but will provide you with statistical torus:


Have no idea how to treat these results. I hope some implications will appear in the comments.


I may also share data on the approximation of the population “layers”

  • 80% — students and teachers (to learn),
  • 5% — freelancers,
  • 3% — state agencies,
  • 2% — girls (judging by their names 😉 ).

One more interesting fact. Database Designer for MySQL is in demand among studying youth and web-freelancers, but Database Designer for PostgreSQL is the matter of significant interest of corporative people. However we didn’t see any strange things that PostgreSQL is seriously considered as an alternative to Oracle, at least, for the new projects.

But we were surprised that nobody asked about support of EnterpriseDB, which is aggressively positioning itself as “PostgreSQL compatible with Oracle”:

Built upon PostgreSQL … offers deeper Oracle® compatibility.


I didn’t include Database Designer for MsSQL in this report because of it’s pre-beta status. «Pre-beta» — is some kind of euphemism for us, application may be used freely — monitor will not blow up. 😉 Just there is no documentation yet and some bugs may reveal themselves.

in Russian

Ой, соскучился я да по языку-то русскому. Ой, истосковался. Все же на буржуинском этом пишу да пишу.

Люди добрые, это где это видано, да чтобы мужик русский не мог бы на блоге своем православном да по-русски душу излить?!

И поди нету над нами славянаме супостата никакого, а не юзаем мы речь нашу.

Фух. Отлегло. Водки бы теперича. Да с селедушкой. 🙂

О.С. Зачал блог тут православный для конторы одной. Не денег ради. Любви к ближнему из-за…