NOTIFY(l) SQL - Language Statements (2002-11-22) NOTIFY(l)
NAME
NOTIFY - generate a notification
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 backend
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 (interprocess
communication) mechanism for a collection of processes
accessing the same PostgreSQL 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 when the table
is changed, and the application programmer can't
Page 1 (printed 3/24/03)
NOTIFY(l) SQL - Language Statements (2002-11-22) NOTIFY(l)
accidentally forget to do it.
NOTIFY interacts with SQL transactions in some important
ways. Firstly, if a NOTIFY is executed inside a transaction,
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 commands 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 listening 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 reasoning is that
if a notify were delivered within a transaction that was
later aborted, one would want the notification 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
listening on the same notify name itself. In that case it
will get back a notify event, just like all the other
listening 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 PostgreSQL 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 message)
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. PostgreSQL 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
correspond to the name of any actual table. If name is
enclosed in double-quotes, it need not even be a
Page 2 (printed 3/24/03)
NOTIFY(l) SQL - Language Statements (2002-11-22) NOTIFY(l)
syntactically valid name, but can be any string up to 63
characters long.
In some previous releases of PostgreSQL, 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 PostgreSQL releases prior to 6.4, the backend PID
delivered in a notify message was always the PID of the
frontend'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.
Page 3 (printed 3/24/03)
