The NOTIFY command is the second half of the LISTEN command, covered earlier in the reference guide. The two commands provide a simple IPC (interprocess communication) implementation that can often prove useful if used correctly.
Use NOTIFY to send out a notification with the specified name; if any frontends have issued a LISTEN command with the same notification name, they will be informed of the notification.
The behavior of a frontend process after receiving a notification sent by the NOTIFY command is dependent upon its implementation of the feature, so it may not respond immediately (or at all).
A notification is comprised of the notification's name and issuing backend's PID. The original designer of the database specifies what notify condition names exist and how they function within the database.
The NOTIFY and LISTEN commands are most often used to provide a way to notify frontend processes that tables have been modified; as such, notification names are often set to the names of tables. This is the common use of these features, but it is not explicitly defined that they must be table names.
Automatic notification of table modifications can be achieved by placing the NOTIFY command in a rule that gets triggered by table updates.
It is important to note how NOTIFY behaves when used with transactions. Most importantly, any NOTIFY commands executed within a transaction will not be delivered until after the transaction is committed. If this behavior were not in place, notifications could be sent out from aborted transactions.
Also important is that a backend will not deliver a notification to its connected frontend if a transaction is in progress. If currently within a transaction, the backend will wait until it has been terminated with either a COMMIT or ROLLBACK before sending it to the frontend.
The NOTIFY/LISTEN system works in a way that is very similar to that of UNIX signals; if the same notification is sent multiple times, it may only be sent to listening processes once, instead of however many times it was signaled.
Because of this design feature, you cannot use the number of received notifications as a counter or to track anything important within your database. The correct way to achieve tracking or counting would be to use NOTIFY with a sequence object (or something similar) to wake appliations and track or count actions and events.
The following examples define a notify event to listen for, then notify the backend process that the event was reached.
booktown=# LISTEN PUBLISHER_UPDATE; LISTEN booktown=# NOTIFY PUBLISHER_UPDATE; Asynchronous NOTIFY 'publisher_update' from backend with pid '16864' received.