{"Entry":{"collection":"sql","key":"notify","name":"NOTIFY","aliases":["notify"],"metadata":{"aliases":["notify"],"changed_in":["6.5","9.0"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":{"added":["NOTIFY name"],"removed":["NOTIFY notifyname"]},"to":"6.5"},{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-notify.htm","to_file":"sql-notify.html"},"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","examples","see_also"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":null,"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":["notes"],"changed":["description","parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["NOTIFY channel [ , payload ]"],"removed":["NOTIFY name"]},"to":"9.0"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.3"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"19"}],"content_hash":"ed3fada5a356154e2ade676ee2230ee6cc124b606c2989cfbe1d93b6fde573cc","editorial":{},"first_version":"6.4","group":"session","imported_at":"2026-09-30T17:43:39.440397+08:00","last_version":"20","name":"NOTIFY","object":"","position":14002,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"generate a notification","purpose_zh":"","related":["listen","unlisten"],"slug":"notify","source_rev":"a709ab85","synopsis":"NOTIFY channel [ , payload ]","verb":"NOTIFY"}},"Definition":{"Collection":"sql","Key":"notify","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"notify","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-NOTIFY","file":"sql-notify.html","lang":"en","name":"NOTIFY","purpose":"generate a notification","purpose_zh":"","related":["listen","unlisten"],"sections":[{"html":"\u003cp\u003eThe \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e command sends a notification event together with an optional \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003epayload\u003c/span\u003e”\u003c/span\u003e string to each client application that has previously executed \u003ccode class=\"command\"\u003eLISTEN \u003cem class=\"replaceable\"\u003e\u003ccode\u003echannel\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e for the specified channel name in the current database. Notifications are visible to all users.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e provides a simple interprocess communication mechanism for a collection of processes accessing the same \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 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).\u003c/p\u003e\u003cp\u003eThe information passed to the client for a notification event includes the notification channel name, the notifying session's server process \u003cacronym\u003ePID\u003c/acronym\u003e, and the payload string, which is an empty string if it has not been specified.\u003c/p\u003e\u003cp\u003eIt 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, \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eI changed this table, take a look at it to see what's new\u003c/span\u003e”\u003c/span\u003e. But no such association is enforced by the \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e and \u003ccode class=\"command\"\u003eLISTEN\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003eWhen \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e is used to signal the occurrence of changes to a particular table, a useful programming technique is to put the \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e interacts with SQL transactions in some important ways. Firstly, if a \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e 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 \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e. 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 \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etake back\u003c/span\u003e”\u003c/span\u003e 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 \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e for real-time signaling should try to keep their transactions short.\u003c/p\u003e\u003cp\u003eIf 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, \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003eIt is common for a client that executes \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e 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 \u003cacronym\u003ePID\u003c/acronym\u003e (supplied in the notification event message) is the same as one's own session's \u003cacronym\u003ePID\u003c/acronym\u003e (available from \u003cspan class=\"application\"\u003elibpq\u003c/span\u003e). When they are the same, the notification event is one's own work bouncing back, and can be ignored.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003echannel\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eName of the notification channel to be signaled (any identifier).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003epayload\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003epayload\u003c/span\u003e”\u003c/span\u003e 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.)\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eThere 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 \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e 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 \u003ccode class=\"command\"\u003eLISTEN\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003eThe function \u003ccode class=\"function\"\u003epg_notification_queue_usage\u003c/code\u003e returns the fraction of the queue that is currently occupied by pending notifications. See \u003ca href=\"/docs/18/functions-info.html\" title=\"9.27. System Information Functions and Operators\"\u003eSection 9.27\u003c/a\u003e for more information.\u003c/p\u003e\u003cp\u003eA transaction that has executed \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e cannot be prepared for two-phase commit.\u003c/p\u003e\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003epg_notify\u003c/h3\u003e\u003cp\u003eTo send a notification you can also use the function \u003ccode class=\"literal\"\u003e\u003ccode class=\"function\"\u003epg_notify\u003c/code\u003e(\u003ccode class=\"type\"\u003etext\u003c/code\u003e, \u003ccode class=\"type\"\u003etext\u003c/code\u003e)\u003c/code\u003e. 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 \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e command if you need to work with non-constant channel names and payloads.\u003c/p\u003e\u003c/div\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eConfigure and execute a listen/notify sequence from \u003cspan class=\"application\"\u003epsql\u003c/span\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eLISTEN virtual;\nNOTIFY virtual;\nAsynchronous notification \"virtual\" received from server process with PID 8448.\nNOTIFY virtual, 'This is the payload';\nAsynchronous notification \"virtual\" with payload \"This is the payload\" received from server process with PID 8448.\n\nLISTEN foo;\nSELECT pg_notify('fo' || 'o', 'pay' || 'load');\nAsynchronous notification \"foo\" with payload \"payload\" received from server process with PID 14728.\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThere is no \u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e statement in the SQL standard.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/listen/?v=18\" title=\"LISTEN\"\u003e\u003cspan class=\"refentrytitle\"\u003eLISTEN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/unlisten/?v=18\" title=\"UNLISTEN\"\u003e\u003cspan class=\"refentrytitle\"\u003eUNLISTEN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/runtime-config-resource.html#GUC-MAX-NOTIFY-QUEUE-PAGES\"\u003emax_notify_queue_pages\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"NOTIFY \u003cem class=\"replaceable\"\u003e\u003ccode\u003echannel\u003c/code\u003e\u003c/em\u003e [ , \u003cem class=\"replaceable\"\u003e\u003ccode\u003epayload\u003c/code\u003e\u003c/em\u003e ]","synopsis_text":"NOTIFY channel [ , payload ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"notify","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"NOTIFY","Summary":"发出一个通知","BodyHTML":"\u003cpre\u003eNOTIFY channel [ , payload ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eNOTIFY\u003c/code\u003e命令向每个客户端应用发送一个通知事件，并可附带一个可选的\u003cspan\u003e“\u003cspan\u003e载荷\u003c/span\u003e”\u003c/span\u003e字符串；这些客户端应用此前都已在当前数据库中针对指定的通道名执行过\u003ccode\u003eLISTEN \u003cem\u003e\u003ccode\u003echannel\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e。通知对所有用户可见。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eNOTIFY\u003c/code\u003e为访问同一个\u003cspan\u003ePostgreSQL\u003c/span\u003e数据库的一组进程提供了一种简单的进程间通信机制。通知中可以附带载荷字符串；如果需要传递结构化数据，也可以借助数据库中的表，将附加数据从通知发送者传递给一个或多个监听者，从而构建用于传递结构化数据的更高层机制。\u003c/p\u003e\u003cp\u003e传递给客户端的通知事件信息包括：通知通道名、发出通知的会话对应的服务器进程\u003cacronym\u003ePID\u003c/acronym\u003e，以及载荷字符串。如果未指定载荷字符串，则该字符串为空串。\u003c/p\u003e\u003cp\u003e在某个数据库中使用哪些通道名以及各自含义，由数据库设计者自行决定。常见的做法是让通道名与数据库中的某个表同名，而通知事件基本上意味着：\u003cspan\u003e“\u003cspan\u003e我改动了这张表，去看看有什么新变化\u003c/span\u003e”\u003c/span\u003e。不过，\u003ccode\u003eNOTIFY\u003c/code\u003e和\u003ccode\u003eLISTEN\u003c/code\u003e命令并不会强制这种关联。例如，数据库设计者可以使用多个不同的通道名，来标识同一张表上的不同类型变更；或者也可以利用载荷字符串区分不同情况。\u003c/p\u003e\u003cp\u003e当\u003ccode\u003eNOTIFY\u003c/code\u003e用于表明某张特定表发生变化时，一种很有用的编程技巧是把\u003ccode\u003eNOTIFY\u003c/code\u003e放进由表更新触发的语句级触发器中。这样一来，每当表发生变化时就会自动发出通知，应用程序员也不容易忘记这么做。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eNOTIFY\u003c/code\u003e会以几种重要方式与 SQL 事务交互。首先，如果在事务内部执行\u003ccode\u003eNOTIFY\u003c/code\u003e，那么只有在事务提交之后，通知事件才会被递送；如果事务被中止，则其中所有命令都不会生效，\u003ccode\u003eNOTIFY\u003c/code\u003e也不例外。这种行为是合理的，但如果你期望通知立即送达，可能会感到意外。其次，如果某个正在监听的会话在事务内部收到了通知信号，那么在该事务结束（提交或中止）之前，通知事件都不会递送给它所连接的客户端。原因同样在于：如果通知在事务内部就已递送，而该事务后来又被中止，我们会希望通知也能被撤销，但服务器一旦把通知发送给客户端，就无法再把它\u003cspan\u003e“\u003cspan\u003e收回\u003c/span\u003e”\u003c/span\u003e。因此，通知事件只会在事务之间递送。由此得出的结论是，使用\u003ccode\u003eNOTIFY\u003c/code\u003e做实时信号的应用，应尽量让事务保持短小。\u003c/p\u003e\u003cp\u003e如果在同一个事务中，针对同一通道名多次发送完全相同载荷字符串的通知，那么监听者只会收到其中一个通知事件。另一方面，载荷字符串不同的通知始终会作为不同通知递送。类似地，来自不同事务的通知也绝不会被折叠为一个通知。除了会丢弃重复通知中较后的那些实例之外，\u003ccode\u003eNOTIFY\u003c/code\u003e还保证来自同一事务的通知会按发送顺序递送；来自不同事务的消息，则会按事务提交顺序递送。\u003c/p\u003e\u003cp\u003e执行\u003ccode\u003eNOTIFY\u003c/code\u003e的客户端自己同时也在监听同一通知通道，这是很常见的情形。在这种情况下，它会像其他监听会话一样收到一个通知事件。根据应用逻辑，这可能导致无用功，例如再次读取自己刚刚写入更新的数据库表。要避免这种额外工作，可以检查通知事件消息中提供的发出通知的服务器进程\u003cacronym\u003ePID\u003c/acronym\u003e，是否与当前会话自身的\u003cacronym\u003ePID\u003c/acronym\u003e（可通过\u003cspan\u003elibpq\u003c/span\u003e获得）相同。如果二者相同，就说明这是当前会话自己发出的通知回送给自己，可以直接忽略。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003echannel\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要发信号的通知通道名称（任意标识符）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003epayload\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e随通知一起传递的\u003cspan\u003e“\u003cspan\u003e载荷\u003c/span\u003e”\u003c/span\u003e字符串。它必须指定为简单的字符串字面值。在默认配置下，它必须短于 8000 字节。（如果需要传递二进制数据或大量信息，最好把它们放入数据库表中，并发送该记录的键。）\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e系统中有一个队列，用来保存那些已经发送但尚未被所有监听会话处理的通知。如果这个队列被塞满，那么调用\u003ccode\u003eNOTIFY\u003c/code\u003e的事务会在提交时失败。该队列非常大（标准安装中为 8GB），几乎足以满足所有用例。不过，如果某个会话执行了\u003ccode\u003eLISTEN\u003c/code\u003e后又长时间停留在一个事务里，就无法进行清理。一旦队列占用达到一半，你就会在日志文件中看到警告，指出究竟是哪个会话阻碍了清理。在这种情况下，应确保该会话结束其当前事务，以便清理能够继续进行。\u003c/p\u003e\u003cp\u003e函数\u003ccode\u003epg_notification_queue_usage\u003c/code\u003e返回当前被待处理通知占用的队列比例。详见\u003ca href=\"/docs/18/functions-info.html\" rel=\"nofollow\"\u003e第 9.27 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e执行过\u003ccode\u003eNOTIFY\u003c/code\u003e的事务不能进入两阶段提交的预备状态。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003epg_notify\u003c/h3\u003e\u003cp\u003e要发送通知，也可以使用函数\u003ccode\u003e\u003ccode\u003epg_notify\u003c/code\u003e(\u003ccode\u003etext\u003c/code\u003e, \u003ccode\u003etext\u003c/code\u003e)\u003c/code\u003e。该函数的第一个参数是通道名，第二个参数是载荷。如果需要处理非常量的通道名和载荷，它会比\u003ccode\u003eNOTIFY\u003c/code\u003e命令更容易使用。\u003c/p\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e在\u003cspan\u003epsql\u003c/span\u003e中配置并执行一组 listen/notify 操作：\u003c/p\u003e\u003cpre\u003eLISTEN virtual;\nNOTIFY virtual;\nAsynchronous notification \u0026#34;virtual\u0026#34; received from server process with PID 8448.\nNOTIFY virtual, \u0026#39;This is the payload\u0026#39;;\nAsynchronous notification \u0026#34;virtual\u0026#34; with payload \u0026#34;This is the payload\u0026#34; received from server process with PID 8448.\n\nLISTEN foo;\nSELECT pg_notify(\u0026#39;fo\u0026#39; || \u0026#39;o\u0026#39;, \u0026#39;pay\u0026#39; || \u0026#39;load\u0026#39;);\nAsynchronous notification \u0026#34;foo\u0026#34; with payload \u0026#34;payload\u0026#34; received from server process with PID 14728.\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准中没有\u003ccode\u003eNOTIFY\u003c/code\u003e语句。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/listen/?v=18\" title=\"LISTEN\" rel=\"nofollow\"\u003e\u003cspan\u003eLISTEN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/unlisten/?v=18\" title=\"UNLISTEN\" rel=\"nofollow\"\u003e\u003cspan\u003eUNLISTEN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/runtime-config-resource.html#GUC-MAX-NOTIFY-QUEUE-PAGES\" rel=\"nofollow\"\u003emax_notify_queue_pages\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"1a2e967a7951fd09481b87ce180cf8fde52a48456d3c70aa7eabdc04d2b6511d","Payload":{"purpose_zh":"发出一个通知","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e命令向每个客户端应用发送一个通知事件，并可附带一个可选的\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e载荷\u003c/span\u003e”\u003c/span\u003e字符串；这些客户端应用此前都已在当前数据库中针对指定的通道名执行过\u003ccode class=\"command\"\u003eLISTEN \u003cem class=\"replaceable\"\u003e\u003ccode\u003echannel\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e。通知对所有用户可见。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e为访问同一个\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e数据库的一组进程提供了一种简单的进程间通信机制。通知中可以附带载荷字符串；如果需要传递结构化数据，也可以借助数据库中的表，将附加数据从通知发送者传递给一个或多个监听者，从而构建用于传递结构化数据的更高层机制。\u003c/p\u003e\u003cp\u003e传递给客户端的通知事件信息包括：通知通道名、发出通知的会话对应的服务器进程\u003cacronym\u003ePID\u003c/acronym\u003e，以及载荷字符串。如果未指定载荷字符串，则该字符串为空串。\u003c/p\u003e\u003cp\u003e在某个数据库中使用哪些通道名以及各自含义，由数据库设计者自行决定。常见的做法是让通道名与数据库中的某个表同名，而通知事件基本上意味着：\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e我改动了这张表，去看看有什么新变化\u003c/span\u003e”\u003c/span\u003e。不过，\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e和\u003ccode class=\"command\"\u003eLISTEN\u003c/code\u003e命令并不会强制这种关联。例如，数据库设计者可以使用多个不同的通道名，来标识同一张表上的不同类型变更；或者也可以利用载荷字符串区分不同情况。\u003c/p\u003e\u003cp\u003e当\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e用于表明某张特定表发生变化时，一种很有用的编程技巧是把\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e放进由表更新触发的语句级触发器中。这样一来，每当表发生变化时就会自动发出通知，应用程序员也不容易忘记这么做。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e会以几种重要方式与 SQL 事务交互。首先，如果在事务内部执行\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e，那么只有在事务提交之后，通知事件才会被递送；如果事务被中止，则其中所有命令都不会生效，\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e也不例外。这种行为是合理的，但如果你期望通知立即送达，可能会感到意外。其次，如果某个正在监听的会话在事务内部收到了通知信号，那么在该事务结束（提交或中止）之前，通知事件都不会递送给它所连接的客户端。原因同样在于：如果通知在事务内部就已递送，而该事务后来又被中止，我们会希望通知也能被撤销，但服务器一旦把通知发送给客户端，就无法再把它\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e收回\u003c/span\u003e”\u003c/span\u003e。因此，通知事件只会在事务之间递送。由此得出的结论是，使用\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e做实时信号的应用，应尽量让事务保持短小。\u003c/p\u003e\u003cp\u003e如果在同一个事务中，针对同一通道名多次发送完全相同载荷字符串的通知，那么监听者只会收到其中一个通知事件。另一方面，载荷字符串不同的通知始终会作为不同通知递送。类似地，来自不同事务的通知也绝不会被折叠为一个通知。除了会丢弃重复通知中较后的那些实例之外，\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e还保证来自同一事务的通知会按发送顺序递送；来自不同事务的消息，则会按事务提交顺序递送。\u003c/p\u003e\u003cp\u003e执行\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e的客户端自己同时也在监听同一通知通道，这是很常见的情形。在这种情况下，它会像其他监听会话一样收到一个通知事件。根据应用逻辑，这可能导致无用功，例如再次读取自己刚刚写入更新的数据库表。要避免这种额外工作，可以检查通知事件消息中提供的发出通知的服务器进程\u003cacronym\u003ePID\u003c/acronym\u003e，是否与当前会话自身的\u003cacronym\u003ePID\u003c/acronym\u003e（可通过\u003cspan class=\"application\"\u003elibpq\u003c/span\u003e获得）相同。如果二者相同，就说明这是当前会话自己发出的通知回送给自己，可以直接忽略。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003echannel\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要发信号的通知通道名称（任意标识符）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003epayload\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e随通知一起传递的\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e载荷\u003c/span\u003e”\u003c/span\u003e字符串。它必须指定为简单的字符串字面值。在默认配置下，它必须短于 8000 字节。（如果需要传递二进制数据或大量信息，最好把它们放入数据库表中，并发送该记录的键。）\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e系统中有一个队列，用来保存那些已经发送但尚未被所有监听会话处理的通知。如果这个队列被塞满，那么调用\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e的事务会在提交时失败。该队列非常大（标准安装中为 8GB），几乎足以满足所有用例。不过，如果某个会话执行了\u003ccode class=\"command\"\u003eLISTEN\u003c/code\u003e后又长时间停留在一个事务里，就无法进行清理。一旦队列占用达到一半，你就会在日志文件中看到警告，指出究竟是哪个会话阻碍了清理。在这种情况下，应确保该会话结束其当前事务，以便清理能够继续进行。\u003c/p\u003e\u003cp\u003e函数\u003ccode class=\"function\"\u003epg_notification_queue_usage\u003c/code\u003e返回当前被待处理通知占用的队列比例。详见\u003ca href=\"/docs/18/functions-info.html\" title=\"9.27. 系统信息函数和操作符\"\u003e第 9.27 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e执行过\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e的事务不能进入两阶段提交的预备状态。\u003c/p\u003e\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003epg_notify\u003c/h3\u003e\u003cp\u003e要发送通知，也可以使用函数\u003ccode class=\"literal\"\u003e\u003ccode class=\"function\"\u003epg_notify\u003c/code\u003e(\u003ccode class=\"type\"\u003etext\u003c/code\u003e, \u003ccode class=\"type\"\u003etext\u003c/code\u003e)\u003c/code\u003e。该函数的第一个参数是通道名，第二个参数是载荷。如果需要处理非常量的通道名和载荷，它会比\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e命令更容易使用。\u003c/p\u003e\u003c/div\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e在\u003cspan class=\"application\"\u003epsql\u003c/span\u003e中配置并执行一组 listen/notify 操作：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eLISTEN virtual;\nNOTIFY virtual;\nAsynchronous notification \"virtual\" received from server process with PID 8448.\nNOTIFY virtual, 'This is the payload';\nAsynchronous notification \"virtual\" with payload \"This is the payload\" received from server process with PID 8448.\n\nLISTEN foo;\nSELECT pg_notify('fo' || 'o', 'pay' || 'load');\nAsynchronous notification \"foo\" with payload \"payload\" received from server process with PID 14728.\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003eSQL 标准中没有\u003ccode class=\"command\"\u003eNOTIFY\u003c/code\u003e语句。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/listen/?v=18\" title=\"LISTEN\"\u003e\u003cspan class=\"refentrytitle\"\u003eLISTEN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/unlisten/?v=18\" title=\"UNLISTEN\"\u003e\u003cspan class=\"refentrytitle\"\u003eUNLISTEN\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/runtime-config-resource.html#GUC-MAX-NOTIFY-QUEUE-PAGES\"\u003emax_notify_queue_pages\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"NOTIFY \u003cem class=\"replaceable\"\u003e\u003ccode\u003echannel\u003c/code\u003e\u003c/em\u003e [ , \u003cem class=\"replaceable\"\u003e\u003ccode\u003epayload\u003c/code\u003e\u003c/em\u003e ]","synopsis_text":"NOTIFY channel [ , payload ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}