How to use LISTEN and NOTIFY PostgreSQL commands in Elixir?

How can you benefit from LISTEN and NOTIFY postgres database features in Elixir applications?

If you need a database feature, Postgres probably has it.

PostgreSQL offers an asynchronous Publish-Subscribe functionality in the form of the LISTEN and NOTIFY commands.

A client registers its interest in a particular channel (a.k.a. topic or event) with the LISTEN command (and can stop listening with the UNLISTEN command). All subscribers listening on a particular event will be notified asynchronously when a NOTIFY command with that topic is executed. A payload can be passed to communicate additional data to the listeners. This is commonly used for sending notifications that table rows have changed.

Publishing

The notification includes a topic name and a payload (in the default configuration it must be shorter than 8000 bytes). The payload is typically a JSON string but, of course, it could be anything specified as a simple string literal.

You can send a notification using the NOTIFY command like:

NOTIFY 'my_event', '{"id": 3, "state": "active"}'

or with the pg_notify() function:

SELECT pg_notify('my_event', '{"id": 3, "state": "active"}');

The function takes the channel name as the first argument and the payload as the second one. It is much easier to use it than the NOTIFY command if you need to work with non-constant channel names and payloads.

Function implementation

PostgreSQL allows you to extend the database functionality with user-defined functions, which are often referred to as stored procedures.

The store procedures can define functions for creating triggers. In addition, they also add many procedural features, e.g. control structures and complex calculation.

A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger . We name it notify_account_changes here.

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for. In our case, we return just the same record the function was triggered by.

You may wonder where NEW or TG_OP variables come from. When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are, among others, exactly:

NEW — Data type RECORD ; a variable holding the new database row for INSERT/UPDATE operations in row-level triggers.

— Data type ; a variable holding the new database row for INSERT/UPDATE operations in row-level triggers. TG_OP — Data type text ; a string of INSERT , UPDATE , DELETE , or TRUNCATE telling for which operation the trigger was fired.

In our procedure, we are calling the pg_notify function inside. accounts_changed is the name of the event to emit, and the contents will be a JSON object like here for example:

{

"operation": "INSERT",

"record": "{\"id\": 1, \"state\": \"active\"}"

}

returned as a text type for the function’s requirements.

Trigger implementation

Once we have our function defined, it’s time to create a corresponding trigger. We will call it accounts_changed , execute it after INSERT or UPDATE on accounts table, and for each row changed, it will call notify_account_changes procedure:

That’s basically all to publish accounts table changes. Let’s start listening on them now, but this time in Elixir.

Subscribing

The subscription happens with the LISTEN command, but typically you have to use driver-specific APIs. We’re gonna use Elixir language and Postgrex library for that purpose.

Let’s create the supervised application firstly:

mix new postgres_pubsub --sup

Add the required libraries in your mix.exs file:

defp deps do

[

{:postgrex, "~> 0.13.5"},

{:poison, "~> 3.0"},

{:ecto, "~> 2.2"}

]

end

And then, compile them:

mix do deps.get, deps.compile, compile

Next, we have to create our Repo module:

There’s nothing special about it initially — just a child specification for the Application :

Listening

As mentioned before, we will leverage Postgrex API to subscribe to PostgreSQL channels. We will connect to our database and listen on a specific event name:

As we can subscribe to the selected topic, we are able to start reacting to it finally too. We have to create a separate worker module for that purpose:

Again, we provided a child specification for our Application so that we can put it there to the children list:

defp children do

[

PostgresPubSub.Repo,

PostgresPubSub.Listener

]

end

However, it’s not enough yet. We just requested to listen on accounts_changed event but not handle this message anywhere. We’re gonna leverage the Listener further to receive the incoming events:

In the handle_info/2 callback from the GenServer module, we are handling notifications from Postgrex . The information passed to the client for a notification event includes:

the notification channel name ( "accounts_changed” )

) the notifying session’s server process PID

the payload string (which is an empty string if it has not been specified)

Then, we parse and log the received JSON payload directly from our Postgres.

Usage

Let’s test how everything is working now. Firstly, we have to prepare accounts migration and only then create our trigger:

We can also create an Account module:

Once all migrations are applied, we can start an interactive session and check if everything works as intended:

➜ postgres_pubsub iex -S mix

Erlang/OTP 21 [erts-10.0.5] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:1] [hipe] [dtrace] Interactive Elixir (1.7.2) - press Ctrl+C to exit (type h() ENTER for help) iex(1)> alias PostgresPubSub.{Account, Repo}

[PostgresPubSub.Account, PostgresPubSub.Repo] iex(2)> Repo.insert(%Account{username: "username"}) 14:08:38.673 [info] %{operation: "INSERT", record: %{id: "843081fe-d31b-4a71-ba76-7052b69aa077", username: "username"}} 14:08:38.673 [debug] QUERY OK db=58.6ms queue=0.1ms

INSERT INTO "accounts" ("username","id") VALUES ($1,$2) ["username", <<132, 48, 129, 254, 211, 27, 74, 113, 186, 118, 112, 82, 182, 154, 160, 119>>]

{:ok,

%Account{

__meta__: #Ecto.Schema.Metadata<:loaded, "accounts">,

id: "843081fe-d31b-4a71-ba76-7052b69aa077",

username: "username"

}}

It works! As you can see, when we insert a new Account we receive a corresponding event from PostgreSQL itself with the same Account .

Subscribe to get the latest content immediately

https://tinyletter.com/KamilLelonek

Summary

In this article, you learned a lot about PostgreSQL notification system and how to work with it in Elixir.

To sum up:

LISTEN

Registers a listener on the notification channel (if the current session is already registered as a listener for this notification channel, nothing is done).

A session can be unregistered for a given notification channel with the UNLISTEN command.

NOTIFY

Sends a notification event together with an optional “payload” string to each client application that has previously executed LISTEN for the specified channel name in the current database.

Whenever invoked, all the sessions currently listening on that notification channel are notified, and each will, in turn, notify its connected client application.

There might be some advantages and drawbacks of the presented solution:

Pros:

Reduces the number of round trips between application and a database . All SQL statements are wrapped inside a function stored in the PostgreSQL itself so the application only has to issue a function call to get the result back instead of sending multiple SQL statements and wait for the result between each call.

. All SQL statements are wrapped inside a function stored in the PostgreSQL itself so the application only has to issue a function call to get the result back instead of sending multiple SQL statements and wait for the result between each call. Increases an application performance because the user-defined functions are pre-compiled and stored in the PostgreSQL DB.

because the user-defined functions are pre-compiled and stored in the PostgreSQL DB. Once you develop a function, you can reuse it in other applications.

Cons: