PostgreSQL notifications with Psycopg2 and Eventlet

Posted by Daniele Varrazzo on 2010-12-01

Tagged as recipe, async, eventlet, notify

PostgreSQL supports asynchronous notifications, a simple messaging system allowing clients to be notified about events occurred in the database. Notifications can be sent by any session using a " NOTIFY channel" command and will be received by any session which has subscribed with a LISTEN channel to receive updates. The system has been greatly improved in PostgreSQL 9.0 with the addition of a message payload, making the feature even more useful. Previously a typical use case would have been to notify interested sessions that a certain table was updated: now it is possible to signal for instance which record was changed. You can put the NOTIFY command in a database trigger for automatic notifications on insert or update... the possibilities are actually quite interesting.

Psycopg2 allows a client program to receive notifications from the database backend. Starting from version 2.3 the notification received will also include the payload, when connected to a PostgreSQL 9.0 server. The notification are received automatically after each query and are made available in the notifies attribute of the connection. They are also received using the poll() method, without the need of issuing queries only to receive notifications.

But polling a connection is not the most efficient nor timely way to receive notifications: a better approach is to ask the OS to wake us up when there is something to read on the connection socket. This approach is particularly appealing when using an event-driven framework such as Twisted or Eventlet, with which is easy to perform other tasks while there is nothing to read on the connection. In "regular" Python programs it is possible to use select() to block until a notification arrives (see the documentation for an example): this would block the entire program. If some concurrency is required it is possible to use the blocking syscall in a thread to allow the rest of the program to carry on.