NOTIFY(l)                                               NOTIFY(l)

NAME
       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
       when the table is changed, and the application  programmer
       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
       in  double-quotes,  it  need  not  even be a syntactically
       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;
       ASYNC NOTIFY of 'virtual' from backend pid '11239' received

COMPATIBILITY
   SQL92
       There is no NOTIFY statement in SQL92.

SQL - Language Statements 15 August 1999                        1