Postgres notification - ghdrako/doc_snipets GitHub Wiki

The PostgreSQL protocol includes a streaming protocol with Copy and also implements asynchronous messages and notifications. This means that as soon as a connection is established with PostgreSQL, the server can send messages to the client even when the client is idle.

PostgreSQL contains a publish/subscribe (pub/sub) messaging system in the form of the LISTEN and NOTIFY commands. Messages are organized into channels and can contain an optional payload. The NOTIFY command is used to send a message, and LISTEN is used to listen for messages.

LISTEN and NOTIFY are generic mechanisms to send notifications to channels with an optional payload. Clients may LISTEN to channels by name, then when a NOTIFY happens for that channel name, listening clients are notified.
PostgreSQL LISTEN and NOTIFY are used as interprocess communication to signal when new background jobs are enqueued and as part of polling a table for new records. Message payloads are sent as JSON-formatted text.

NOTIFY

NOTIFY For publishing, a pattern suggested by the PostgreSQL documentation is a trigger attached to a table to generate messages.

CREATE FUNCTION send_notification()  RETURNS TRIGGER AS $$ 
BEGIN  
  PERFORM pg_notify('my_channel_name', row_to_json(new)::TEXT);
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER notify_table_updated  
  AFTER INSERT OR UPDATE  
  ON your_table  
  FOR EACH ROW 
EXECUTE FUNCTION send_notification();

Starting from the bottom, a trigger is attached to a table, in the example it’s your_ table. You’ll also name the trigger, rather than my placeholder’s name of notify_table_ updated. For each row that is inserted or updated, the send_notification function will be invoked. This is boilerplate for creating a trigger on a table; the action to perform is separate from creating the trigger on a table as this allows the function to be reused across multiple tables. The send_notification function is the action. It is a function defined as a trigger. It performs a single task, invoking the pg_notify function. This function is equivalent to the NOTIFY command; it is used instead of NOTIFY as is easier to use with nonconstant channel names or payloads, which the example is doing. The channel name is my_channel_name with a payload being a JSON representation of the modified row. In triggers, new refers to the new state of the data and we pass that to row_to_json to convert it to JSON, followed by a cast back to TEXT as that’s the datatype required by pg_notify. Another benefit of using NOTIFY is that it is transactional. Messages sent within a transaction are not delivered until the transaction has committed. If the transaction is rolled back, nothing ever delivered. There is also nothing wrong with explicitly calling NOTIFY from application code. I prefer the trigger approach for most usages as the event is often related to data changes in a table and coupling it to the table within the database ensures that they are always generated. There are situations where I want to send messages that are unrelated to table changes, such as passing heartbeat messages across the cluster.

LISTEN

For subscribing, the LISTEN command is used. It takes a single parameter, the name of the channel to listen on. A connection may listen to multiple channels at once. As this functionality is outside of the SQL standard, access to events is a proprietary feature in PostgreSQL drivers. It isn’t exposed in the database agnostic APIs that many platforms offer. A connection that is LISTEN-ing should be dedicated for this purpose. The returned data structure will vary slightly according to the client API in use, but a commonality with all of them will be access to the channel name and payload.

R2DBC The R2DBC API was created to extend reactive programming through to interacting with relational databases. The PostgreSQL driver for R2DBC has one feature that makes me very happy – the ability to get the results of a LISTEN command as a Project Reactor Flux. This allows PostgreSQL’s pub/sub messages to be consumed as messages within a Reactive Stream

PostgreSQL Notifications LISTEN/NOTIFY

sql# listen channel;
LISTEN  
sql# notify channel, 'foo';
NOTIFY 
Asynchronous notification "channel" with payload "foo" ⏎ 
received from server process with PID 40430.

Note that the message could be sent from another connection, so try it and see with several psql instances. The payload from the message can be any text, up to 8kB in length. This allows for rich messages to flow, such as JSON encoded values.

yesql# listen channel;
LISTEN

yesql# notify channel, 'foo';

NOTIFY Programming

Server-side Prepared Statements

In console:

prepare foo as
 select date, shares, trades, dollars
   from factbook
  where date >= $1::date
    and date < $1::date + interval '1 month'
   order by date;

 execute foo('2010-02-01');