3
PostgreSQL documentation
6
<refentry id="SQL-NOTIFY">
8
<refentrytitle id="sql-notify-title">NOTIFY</refentrytitle>
9
<manvolnum>7</manvolnum>
10
<refmiscinfo>SQL - Language Statements</refmiscinfo>
14
<refname>NOTIFY</refname>
15
<refpurpose>generate a notification</refpurpose>
18
<indexterm zone="sql-notify">
19
<primary>NOTIFY</primary>
24
NOTIFY <replaceable class="PARAMETER">name</replaceable>
29
<title>Description</title>
32
The <command>NOTIFY</command> command sends a notification event to each
33
client application that has previously executed
34
<command>LISTEN <replaceable class="parameter">name</replaceable></command>
35
for the specified notification name in the current database.
39
<command>NOTIFY</command> provides a simple form of signal or
40
interprocess communication mechanism for a collection of processes
41
accessing the same <productname>PostgreSQL</productname> database.
42
Higher-level mechanisms can be built by using tables in the database to
43
pass additional data (beyond a mere notification name) from notifier to
48
The information passed to the client for a notification event includes the notification
49
name and the notifying session's server process <acronym>PID</>. It is up to the
50
database designer to define the notification names that will be used in a given
51
database and what each one means.
55
Commonly, the notification name is the same as the name of some table in
56
the database, and the notify event essentially means, <quote>I changed this table,
57
take a look at it to see what's new</quote>. But no such association is enforced by
58
the <command>NOTIFY</command> and <command>LISTEN</command> commands. For
59
example, a database designer could use several different notification names
60
to signal different sorts of changes to a single table.
64
When <command>NOTIFY</command> is used to signal the occurrence of changes
65
to a particular table, a useful programming technique is to put the
66
<command>NOTIFY</command> in a rule that is triggered by table updates.
67
In this way, notification happens automatically when the table is changed,
68
and the application programmer cannot accidentally forget to do it.
72
<command>NOTIFY</command> interacts with SQL transactions in some important
73
ways. Firstly, if a <command>NOTIFY</command> is executed inside a
74
transaction, the notify events are not delivered until and unless the
75
transaction is committed. This is appropriate, since if the transaction
76
is aborted, all the commands within it have had no
77
effect, including <command>NOTIFY</command>. But it can be disconcerting if one
78
is expecting the notification events to be delivered immediately. Secondly, if
79
a listening session receives a notification signal while it is within a transaction,
80
the notification event will not be delivered to its connected client until just
81
after the transaction is completed (either committed or aborted). Again, the
82
reasoning is that if a notification were delivered within a transaction that was
83
later aborted, one would want the notification to be undone somehow —
85
the server cannot <quote>take back</quote> a notification once it has sent it to the client.
86
So notification events are only delivered between transactions. The upshot of this
87
is that applications using <command>NOTIFY</command> for real-time signaling
88
should try to keep their transactions short.
92
<command>NOTIFY</command> behaves like Unix signals in one important
93
respect: if the same notification name is signaled multiple times in quick
94
succession, recipients might get only one notification event for several executions
95
of <command>NOTIFY</command>. So it is a bad idea to depend on the number
96
of notifications received. Instead, use <command>NOTIFY</command> to wake up
97
applications that need to pay attention to something, and use a database
98
object (such as a sequence) to keep track of what happened or how many times
103
It is common for a client that executes <command>NOTIFY</command>
104
to be listening on the same notification name itself. In that case
105
it will get back a notification event, just like all the other
106
listening sessions. Depending on the application logic, this could
107
result in useless work, for example, reading a database table to
108
find the same updates that that session just wrote out. It is
109
possible to avoid such extra work by noticing whether the notifying
110
session's server process <acronym>PID</> (supplied in the
111
notification event message) is the same as one's own session's
112
<acronym>PID</> (available from <application>libpq</>). When they
113
are the same, the notification event is one's own work bouncing
114
back, and can be ignored. (Despite what was said in the preceding
115
paragraph, this is a safe technique.
116
<productname>PostgreSQL</productname> keeps self-notifications
117
separate from notifications arriving from other sessions, so you
118
cannot miss an outside notification by ignoring your own
124
<title>Parameters</title>
128
<term><replaceable class="PARAMETER">name</replaceable></term>
131
Name of the notification to be signaled (any identifier).
139
<title>Examples</title>
142
Configure and execute a listen/notify sequence from
143
<application>psql</application>:
148
Asynchronous notification "virtual" received from server process with PID 8448.
154
<title>Compatibility</title>
157
There is no <command>NOTIFY</command> statement in the SQL
163
<title>See Also</title>
165
<simplelist type="inline">
166
<member><xref linkend="sql-listen" endterm="sql-listen-title"></member>
167
<member><xref linkend="sql-unlisten" endterm="sql-unlisten-title"></member>