| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/notify.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-notify"> |
| 7 |
<indexterm zone="sql-notify"> |
| 8 |
<primary>NOTIFY</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>NOTIFY</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>NOTIFY</refname> |
| 19 |
<refpurpose>generate a notification</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable class="parameter">payload</replaceable> ] |
| 25 |
</synopsis> |
| 26 |
</refsynopsisdiv> |
| 27 |
|
| 28 |
<refsect1> |
| 29 |
<title>Description</title> |
| 30 |
|
| 31 |
<para> |
| 32 |
The <command>NOTIFY</command> command sends a notification event together |
| 33 |
with an optional <quote>payload</quote> string to each client application that |
| 34 |
has previously executed |
| 35 |
<command>LISTEN <replaceable class="parameter">channel</replaceable></command> |
| 36 |
for the specified channel name in the current database. |
| 37 |
Notifications are visible to all users. |
| 38 |
</para> |
| 39 |
|
| 40 |
<para> |
| 41 |
<command>NOTIFY</command> provides a simple |
| 42 |
interprocess communication mechanism for a collection of processes |
| 43 |
accessing the same <productname>PostgreSQL</productname> database. |
| 44 |
A payload string can be sent along with the notification, and |
| 45 |
higher-level mechanisms for passing structured data can be built by using |
| 46 |
tables in the database to pass additional data from notifier to listener(s). |
| 47 |
</para> |
| 48 |
|
| 49 |
<para> |
| 50 |
The information passed to the client for a notification event includes the |
| 51 |
notification channel |
| 52 |
name, the notifying session's server process <acronym>PID</acronym>, and the |
| 53 |
payload string, which is an empty string if it has not been specified. |
| 54 |
</para> |
| 55 |
|
| 56 |
<para> |
| 57 |
It is up to the database designer to define the channel names that will |
| 58 |
be used in a given database and what each one means. |
| 59 |
Commonly, the channel name is the same as the name of some table in |
| 60 |
the database, and the notify event essentially means, <quote>I changed this table, |
| 61 |
take a look at it to see what's new</quote>. But no such association is enforced by |
| 62 |
the <command>NOTIFY</command> and <command>LISTEN</command> commands. For |
| 63 |
example, a database designer could use several different channel names |
| 64 |
to signal different sorts of changes to a single table. Alternatively, |
| 65 |
the payload string could be used to differentiate various cases. |
| 66 |
</para> |
| 67 |
|
| 68 |
<para> |
| 69 |
When <command>NOTIFY</command> is used to signal the occurrence of changes |
| 70 |
to a particular table, a useful programming technique is to put the |
| 71 |
<command>NOTIFY</command> in a statement trigger that is triggered by table updates. |
| 72 |
In this way, notification happens automatically when the table is changed, |
| 73 |
and the application programmer cannot accidentally forget to do it. |
| 74 |
</para> |
| 75 |
|
| 76 |
<para> |
| 77 |
<command>NOTIFY</command> interacts with SQL transactions in some important |
| 78 |
ways. Firstly, if a <command>NOTIFY</command> is executed inside a |
| 79 |
transaction, the notify events are not delivered until and unless the |
| 80 |
transaction is committed. This is appropriate, since if the transaction |
| 81 |
is aborted, all the commands within it have had no |
| 82 |
effect, including <command>NOTIFY</command>. But it can be disconcerting if one |
| 83 |
is expecting the notification events to be delivered immediately. Secondly, if |
| 84 |
a listening session receives a notification signal while it is within a transaction, |
| 85 |
the notification event will not be delivered to its connected client until just |
| 86 |
after the transaction is completed (either committed or aborted). Again, the |
| 87 |
reasoning is that if a notification were delivered within a transaction that was |
| 88 |
later aborted, one would want the notification to be undone somehow — |
| 89 |
but |
| 90 |
the server cannot <quote>take back</quote> a notification once it has sent it to the client. |
| 91 |
So notification events are only delivered between transactions. The upshot of this |
| 92 |
is that applications using <command>NOTIFY</command> for real-time signaling |
| 93 |
should try to keep their transactions short. |
| 94 |
</para> |
| 95 |
|
| 96 |
<para> |
| 97 |
If the same channel name is signaled multiple times with identical |
| 98 |
payload strings within the same transaction, only one instance of the |
| 99 |
notification event is delivered to listeners. |
| 100 |
On the other hand, notifications with distinct payload strings will |
| 101 |
always be delivered as distinct notifications. Similarly, notifications from |
| 102 |
different transactions will never get folded into one notification. |
| 103 |
Except for dropping later instances of duplicate notifications, |
| 104 |
<command>NOTIFY</command> guarantees that notifications from the same |
| 105 |
transaction get delivered in the order they were sent. It is also |
| 106 |
guaranteed that messages from different transactions are delivered in |
| 107 |
the order in which the transactions committed. |
| 108 |
</para> |
| 109 |
|
| 110 |
<para> |
| 111 |
It is common for a client that executes <command>NOTIFY</command> |
| 112 |
to be listening on the same notification channel itself. In that case |
| 113 |
it will get back a notification event, just like all the other |
| 114 |
listening sessions. Depending on the application logic, this could |
| 115 |
result in useless work, for example, reading a database table to |
| 116 |
find the same updates that that session just wrote out. It is |
| 117 |
possible to avoid such extra work by noticing whether the notifying |
| 118 |
session's server process <acronym>PID</acronym> (supplied in the |
| 119 |
notification event message) is the same as one's own session's |
| 120 |
<acronym>PID</acronym> (available from <application>libpq</application>). When they |
| 121 |
are the same, the notification event is one's own work bouncing |
| 122 |
back, and can be ignored. |
| 123 |
</para> |
| 124 |
</refsect1> |
| 125 |
|
| 126 |
<refsect1> |
| 127 |
<title>Parameters</title> |
| 128 |
|
| 129 |
<variablelist> |
| 130 |
<varlistentry> |
| 131 |
<term><replaceable class="parameter">channel</replaceable></term> |
| 132 |
<listitem> |
| 133 |
<para> |
| 134 |
Name of the notification channel to be signaled (any identifier). |
| 135 |
</para> |
| 136 |
</listitem> |
| 137 |
</varlistentry> |
| 138 |
<varlistentry> |
| 139 |
<term><replaceable class="parameter">payload</replaceable></term> |
| 140 |
<listitem> |
| 141 |
<para> |
| 142 |
The <quote>payload</quote> string to be communicated along with the |
| 143 |
notification. This must be specified as a simple string literal. |
| 144 |
In the default configuration it must be shorter than 8000 bytes. |
| 145 |
(If binary data or large amounts of information need to be communicated, |
| 146 |
it's best to put it in a database table and send the key of the record.) |
| 147 |
</para> |
| 148 |
</listitem> |
| 149 |
</varlistentry> |
| 150 |
</variablelist> |
| 151 |
</refsect1> |
| 152 |
|
| 153 |
<refsect1> |
| 154 |
<title>Notes</title> |
| 155 |
|
| 156 |
<para> |
| 157 |
There is a queue that holds notifications that have been sent but not |
| 158 |
yet processed by all listening sessions. If this queue becomes full, |
| 159 |
transactions calling <command>NOTIFY</command> will fail at commit. |
| 160 |
The queue is quite large (8GB in a standard installation) and should be |
| 161 |
sufficiently sized for almost every use case. However, no cleanup can take |
| 162 |
place if a session executes <command>LISTEN</command> and then enters a |
| 163 |
transaction for a very long time. Once the queue is half full you will see |
| 164 |
warnings in the log file pointing you to the session that is preventing |
| 165 |
cleanup. In this case you should make sure that this session ends its |
| 166 |
current transaction so that cleanup can proceed. |
| 167 |
</para> |
| 168 |
<para> |
| 169 |
The function <function>pg_notification_queue_usage</function> returns the |
| 170 |
fraction of the queue that is currently occupied by pending notifications. |
| 171 |
See <xref linkend="functions-info"/> for more information. |
| 172 |
</para> |
| 173 |
<para> |
| 174 |
A transaction that has executed <command>NOTIFY</command> cannot be |
| 175 |
prepared for two-phase commit. |
| 176 |
</para> |
| 177 |
|
| 178 |
<refsect2> |
| 179 |
<title>pg_notify</title> |
| 180 |
|
| 181 |
<indexterm> |
| 182 |
<primary>pg_notify</primary> |
| 183 |
</indexterm> |
| 184 |
|
| 185 |
<para> |
| 186 |
To send a notification you can also use the function |
| 187 |
<literal><function>pg_notify</function>(<type>text</type>, |
| 188 |
<type>text</type>)</literal>. The function takes the channel name as the |
| 189 |
first argument and the payload as the second. The function is much easier |
| 190 |
to use than the <command>NOTIFY</command> command if you need to work with |
| 191 |
non-constant channel names and payloads. |
| 192 |
</para> |
| 193 |
</refsect2> |
| 194 |
</refsect1> |
| 195 |
|
| 196 |
<refsect1> |
| 197 |
<title>Examples</title> |
| 198 |
|
| 199 |
<para> |
| 200 |
Configure and execute a listen/notify sequence from |
| 201 |
<application>psql</application>: |
| 202 |
|
| 203 |
<programlisting> |
| 204 |
LISTEN virtual; |
| 205 |
NOTIFY virtual; |
| 206 |
Asynchronous notification "virtual" received from server process with PID 8448. |
| 207 |
NOTIFY virtual, 'This is the payload'; |
| 208 |
Asynchronous notification "virtual" with payload "This is the payload" received from server process with PID 8448. |
| 209 |
|
| 210 |
LISTEN foo; |
| 211 |
SELECT pg_notify('fo' || 'o', 'pay' || 'load'); |
| 212 |
Asynchronous notification "foo" with payload "payload" received from server process with PID 14728. |
| 213 |
</programlisting></para> |
| 214 |
</refsect1> |
| 215 |
|
| 216 |
<refsect1> |
| 217 |
<title>Compatibility</title> |
| 218 |
|
| 219 |
<para> |
| 220 |
There is no <command>NOTIFY</command> statement in the SQL |
| 221 |
standard. |
| 222 |
</para> |
| 223 |
</refsect1> |
| 224 |
|
| 225 |
<refsect1> |
| 226 |
<title>See Also</title> |
| 227 |
|
| 228 |
<simplelist type="inline"> |
| 229 |
<member><xref linkend="sql-listen"/></member> |
| 230 |
<member><xref linkend="sql-unlisten"/></member> |
| 231 |
<member><xref linkend="guc-max-notify-queue-pages"/></member> |
| 232 |
</simplelist> |
| 233 |
</refsect1> |
| 234 |
</refentry> |