summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-notify.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/sql-notify.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/sql-notify.html')
-rw-r--r--doc/src/sgml/html/sql-notify.html132
1 files changed, 132 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-notify.html b/doc/src/sgml/html/sql-notify.html
new file mode 100644
index 0000000..0c03195
--- /dev/null
+++ b/doc/src/sgml/html/sql-notify.html
@@ -0,0 +1,132 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>NOTIFY</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-move.html" title="MOVE" /><link rel="next" href="sql-prepare.html" title="PREPARE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">NOTIFY</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-move.html" title="MOVE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-prepare.html" title="PREPARE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-NOTIFY"><div class="titlepage"></div><a id="id-1.9.3.158.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">NOTIFY</span></h2><p>NOTIFY — generate a notification</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+NOTIFY <em class="replaceable"><code>channel</code></em> [ , <em class="replaceable"><code>payload</code></em> ]
+</pre></div><div class="refsect1" id="id-1.9.3.158.5"><h2>Description</h2><p>
+ The <code class="command">NOTIFY</code> command sends a notification event together
+ with an optional <span class="quote">“<span class="quote">payload</span>”</span> string to each client application that
+ has previously executed
+ <code class="command">LISTEN <em class="replaceable"><code>channel</code></em></code>
+ for the specified channel name in the current database.
+ Notifications are visible to all users.
+ </p><p>
+ <code class="command">NOTIFY</code> provides a simple
+ interprocess communication mechanism for a collection of processes
+ accessing the same <span class="productname">PostgreSQL</span> database.
+ A payload string can be sent along with the notification, and
+ higher-level mechanisms for passing structured data can be built by using
+ tables in the database to pass additional data from notifier to listener(s).
+ </p><p>
+ The information passed to the client for a notification event includes the
+ notification channel
+ name, the notifying session's server process <acronym class="acronym">PID</acronym>, and the
+ payload string, which is an empty string if it has not been specified.
+ </p><p>
+ It is up to the database designer to define the channel names that will
+ be used in a given database and what each one means.
+ Commonly, the channel name is the same as the name of some table in
+ the database, and the notify event essentially means, <span class="quote">“<span class="quote">I changed this table,
+ take a look at it to see what's new</span>”</span>. But no such association is enforced by
+ the <code class="command">NOTIFY</code> and <code class="command">LISTEN</code> commands. For
+ example, a database designer could use several different channel names
+ to signal different sorts of changes to a single table. Alternatively,
+ the payload string could be used to differentiate various cases.
+ </p><p>
+ When <code class="command">NOTIFY</code> is used to signal the occurrence of changes
+ to a particular table, a useful programming technique is to put the
+ <code class="command">NOTIFY</code> in a statement trigger that is triggered by table updates.
+ In this way, notification happens automatically when the table is changed,
+ and the application programmer cannot accidentally forget to do it.
+ </p><p>
+ <code class="command">NOTIFY</code> interacts with SQL transactions in some important
+ ways. Firstly, if a <code class="command">NOTIFY</code> 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, all the commands within it have had no
+ effect, including <code class="command">NOTIFY</code>. But it can be disconcerting if one
+ is expecting the notification events to be delivered immediately. Secondly, if
+ a listening session receives a notification signal while it is within a transaction,
+ the notification event will not be delivered to its connected client until just
+ after the transaction is completed (either committed or aborted). Again, the
+ reasoning is that if a notification were delivered within a transaction that was
+ later aborted, one would want the notification to be undone somehow —
+ but
+ the server cannot <span class="quote">“<span class="quote">take back</span>”</span> a notification once it has sent it to the client.
+ So notification events are only delivered between transactions. The upshot of this
+ is that applications using <code class="command">NOTIFY</code> for real-time signaling
+ should try to keep their transactions short.
+ </p><p>
+ If the same channel name is signaled multiple times with identical
+ payload strings within the same transaction, only one instance of the
+ notification event is delivered to listeners.
+ On the other hand, notifications with distinct payload strings will
+ always be delivered as distinct notifications. Similarly, notifications from
+ different transactions will never get folded into one notification.
+ Except for dropping later instances of duplicate notifications,
+ <code class="command">NOTIFY</code> guarantees that notifications from the same
+ transaction get delivered in the order they were sent. It is also
+ guaranteed that messages from different transactions are delivered in
+ the order in which the transactions committed.
+ </p><p>
+ It is common for a client that executes <code class="command">NOTIFY</code>
+ to be listening on the same notification channel itself. In that case
+ it will get back a notification event, just like all the other
+ listening sessions. Depending on the application logic, this could
+ result in useless work, for example, reading a database table to
+ find the same updates that that session just wrote out. It is
+ possible to avoid such extra work by noticing whether the notifying
+ session's server process <acronym class="acronym">PID</acronym> (supplied in the
+ notification event message) is the same as one's own session's
+ <acronym class="acronym">PID</acronym> (available from <span class="application">libpq</span>). When they
+ are the same, the notification event is one's own work bouncing
+ back, and can be ignored.
+ </p></div><div class="refsect1" id="id-1.9.3.158.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>channel</code></em></span></dt><dd><p>
+ Name of the notification channel to be signaled (any identifier).
+ </p></dd><dt><span class="term"><em class="replaceable"><code>payload</code></em></span></dt><dd><p>
+ The <span class="quote">“<span class="quote">payload</span>”</span> string to be communicated along with the
+ notification. This must be specified as a simple string literal.
+ In the default configuration it must be shorter than 8000 bytes.
+ (If binary data or large amounts of information need to be communicated,
+ it's best to put it in a database table and send the key of the record.)
+ </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.158.7"><h2>Notes</h2><p>
+ There is a queue that holds notifications that have been sent but not
+ yet processed by all listening sessions. If this queue becomes full,
+ transactions calling <code class="command">NOTIFY</code> will fail at commit.
+ The queue is quite large (8GB in a standard installation) and should be
+ sufficiently sized for almost every use case. However, no cleanup can take
+ place if a session executes <code class="command">LISTEN</code> and then enters a
+ transaction for a very long time. Once the queue is half full you will see
+ warnings in the log file pointing you to the session that is preventing
+ cleanup. In this case you should make sure that this session ends its
+ current transaction so that cleanup can proceed.
+ </p><p>
+ The function <code class="function">pg_notification_queue_usage</code> returns the
+ fraction of the queue that is currently occupied by pending notifications.
+ See <a class="xref" href="functions-info.html" title="9.26. System Information Functions and Operators">Section 9.26</a> for more information.
+ </p><p>
+ A transaction that has executed <code class="command">NOTIFY</code> cannot be
+ prepared for two-phase commit.
+ </p><div class="refsect2" id="id-1.9.3.158.7.5"><h3>pg_notify</h3><a id="id-1.9.3.158.7.5.2" class="indexterm"></a><p>
+ To send a notification you can also use the function
+ <code class="literal"><code class="function">pg_notify</code>(<code class="type">text</code>,
+ <code class="type">text</code>)</code>. The function takes the channel name as the
+ first argument and the payload as the second. The function is much easier
+ to use than the <code class="command">NOTIFY</code> command if you need to work with
+ non-constant channel names and payloads.
+ </p></div></div><div class="refsect1" id="id-1.9.3.158.8"><h2>Examples</h2><p>
+ Configure and execute a listen/notify sequence from
+ <span class="application">psql</span>:
+
+</p><pre class="programlisting">
+LISTEN virtual;
+NOTIFY virtual;
+Asynchronous notification "virtual" received from server process with PID 8448.
+NOTIFY virtual, 'This is the payload';
+Asynchronous notification "virtual" with payload "This is the payload" received from server process with PID 8448.
+
+LISTEN foo;
+SELECT pg_notify('fo' || 'o', 'pay' || 'load');
+Asynchronous notification "foo" with payload "payload" received from server process with PID 14728.
+</pre></div><div class="refsect1" id="id-1.9.3.158.9"><h2>Compatibility</h2><p>
+ There is no <code class="command">NOTIFY</code> statement in the SQL
+ standard.
+ </p></div><div class="refsect1" id="id-1.9.3.158.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-listen.html" title="LISTEN"><span class="refentrytitle">LISTEN</span></a>, <a class="xref" href="sql-unlisten.html" title="UNLISTEN"><span class="refentrytitle">UNLISTEN</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-move.html" title="MOVE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-prepare.html" title="PREPARE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">MOVE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> PREPARE</td></tr></table></div></body></html> \ No newline at end of file