NOTIFY - Signals all frontends and backends listening on a
notify condition
SYNOPSIS
NOTIFY name
INPUTS
notifyname
Notify condition to be signaled.
OUTPUTS
NOTIFY Acknowledgement that notify command has executed.
Notify events
Events are delivered to listening frontends;
whether and how each frontend application reacts
depends on its programming.
DESCRIPTION
The NOTIFY command sends a notify event to each frontend
application that has previously executed LISTEN notifyname
for the specified notify condition in the current
database.
The information passed to the frontend for a notify event
includes the notify condition name and the notifying back
end process's PID. It is up to the database designer to
define the condition names that will be used in a given
database and what each one means.
Commonly, the notify condition name is the same as the
name of some table in the database, and the notify event
essentially means "I changed this table, take a look at it
to see what's new". But no such association is enforced by
the NOTIFY and LISTEN commands. For example, a database
designer could use several different condition names to
signal different sorts of changes to a single table.
NOTIFY provides a simple form of signal or IPC (interpro
cess communication) mechanism for a collection of pro
cesses accessing the same Postgres database. Higher-level
mechanisms can be built by using tables in the database to
pass additional data (beyond a mere condition name) from
notifier to listener(s).
When NOTIFY is used to signal the occurrence of changes to
a particular table, a useful programming technique is to
put the NOTIFY in a rule that is triggered by table
updates. In this way, notification happens automatically
can't accidentally forget to do it.
NOTIFY interacts with SQL transactions in some important
ways. Firstly, if a NOTIFY is executed inside a transac
tion, the notify events are not delivered until and unless
the transaction is committed. This is appropriate, since
if the transaction is aborted we would like all the com
mands within it to have had no effect, including NOTIFY.
But it can be disconcerting if one is expecting the notify
events to be delivered immediately. Secondly, if a listen
ing backend receives a notify signal while it is within a
transaction, the notify event will not be delivered to its
connected frontend until just after the transaction is
completed (either committed or aborted). Again, the rea
soning is that if a notify were delivered within a trans
action that was later aborted, one would want the notifi
cation to be undone somehow---but the backend cannot "take
back" a notify once it has sent it to the frontend. So
notify events are only delivered between transactions. The
upshot of this is that applications using NOTIFY for real-
time signaling should try to keep their transactions
short.
NOTIFY behaves like Unix signals in one important respect:
if the same condition name is signaled multiple times in
quick succession, recipients may get only one notify event
for several executions of NOTIFY. So it is a bad idea to
depend on the number of notifies received. Instead, use
NOTIFY to wake up applications that need to pay attention
to something, and use a database object (such as a
sequence) to keep track of what happened or how many times
it happened.
It is common for a frontend that sends NOTIFY to be lis
tening on the same notify name itself. In that case it
will get back a notify event, just like all the other lis
tening frontends. Depending on the application logic, this
could result in useless work---for example, re-reading a
database table to find the same updates that that frontend
just wrote out. In Postgres 6.4 and later, it is possible
to avoid such extra work by noticing whether the notifying
backend process's PID (supplied in the notify event mes
sage) is the same as one's own backend's PID (available
from libpq). When they are the same, the notify event is
one's own work bouncing back, and can be ignored. (Despite
what was said in the preceding paragraph, this is a safe
technique. Postgres keeps self-notifies separate from
notifies arriving from other backends, so you cannot miss
an outside notify by ignoring your own notifies.)
NOTES
name can be any string valid as a name; it need not corre
spond to the name of any actual table. If name is enclosed
valid name, but can be any string up to 31 characters
long.
In some previous releases of Postgres, name had to be
enclosed in double-quotes when it did not correspond to
any existing table name, even if syntactically valid as a
name. That is no longer required.
In Postgres releases prior to 6.4, the backend PID deliv
ered in a notify message was always the PID of the fron
tend's own backend. So it was not possible to distinguish
one's own notifies from other clients' notifies in those
earlier releases.
USAGE
Configure and execute a listen/notify sequence from psql:
LISTEN virtual;
NOTIFY virtual;
Asynchronous NOTIFY 'virtual' from backend with pid '8448' received.
COMPATIBILITY
SQL92
There is no NOTIFY statement in SQL92.
Man(1) output converted with
man2html