LISTEN and NOTIFY for pub-sub in PostgreSQL

Introduction

LISTEN and NOTIFY let different parts of a database talk to each other instantly. This helps programs react quickly when something changes.

You want to update a user interface right after data changes in the database.

You need to alert other parts of your app when a new order is placed.

You want to trigger background tasks after a database event happens.

You want to avoid constant checking for changes by using real-time notifications.

Syntax

PostgreSQL

LISTEN channel_name;

NOTIFY channel_name, 'optional_message';

LISTEN waits for notifications on a named channel.

NOTIFY sends a message to all listeners on that channel.

Examples

This command makes your session wait for notifications on the 'new_order' channel.

PostgreSQL

LISTEN new_order;

This sends a message 'Order #123 placed' to all sessions listening on 'new_order'.

PostgreSQL

NOTIFY new_order, 'Order #123 placed';

You can send a notification without a message. Listeners just know something happened.

PostgreSQL

NOTIFY new_order;

Sample Program

In one session, you run LISTEN to wait for messages on 'new_order'. In another session, you run NOTIFY to send a message. The first session immediately gets the message.

PostgreSQL

-- Open two database sessions.
-- Session 1: Listen for notifications
LISTEN new_order;

-- Session 2: Send a notification
NOTIFY new_order, 'Order #123 placed';

-- Back to Session 1, you will see a notification received message.

Important Notes

LISTEN and NOTIFY are asynchronous; your session must be actively waiting to receive notifications.

Notifications are lightweight and fast but not guaranteed to be delivered if the listener disconnects.

Use LISTEN and NOTIFY to reduce polling and improve real-time responsiveness in your apps.

Summary

LISTEN waits for messages on a channel.

NOTIFY sends messages to all listeners on that channel.

This helps different parts of your app communicate instantly through the database.

Practice

1. What does the LISTEN command do in PostgreSQL's pub-sub system?

easy

A. It sends a message to all clients listening on a channel.

B. It deletes all notifications from a channel.

C. It creates a new database channel for communication.

D. It waits for notifications on a specified channel.

Solution

1. Step 1: Understand the role of LISTEN

   The LISTEN command makes the current session wait for notifications on a given channel.

2. Step 2: Differentiate from NOTIFY

   NOTIFY sends messages, while LISTEN waits to receive them.

3. Final Answer:

   It waits for notifications on a specified channel. -> Option D

4. Quick Check:

   LISTEN = wait for messages [OK]

Hint: LISTEN waits, NOTIFY sends messages [OK]

Common Mistakes:

• Confusing LISTEN with NOTIFY
• Thinking LISTEN creates channels
• Assuming LISTEN deletes notifications

2. Which of the following is the correct syntax to listen on a channel named updates?

easy

A. NOTIFY updates;

B. LISTEN updates;

C. SUBSCRIBE updates;

D. WAIT FOR updates;

Solution

1. Step 1: Recall the syntax for LISTEN

   The correct syntax to listen on a channel is LISTEN channel_name;.

2. Step 2: Check each option

   LISTEN updates; matches the correct syntax: LISTEN updates;. Others are invalid commands.

3. Final Answer:

   LISTEN updates; -> Option B

4. Quick Check:

   LISTEN syntax = LISTEN channel_name; [OK]

Hint: LISTEN uses 'LISTEN channel;' syntax [OK]

Common Mistakes:

• Using NOTIFY instead of LISTEN
• Using non-existent commands like SUBSCRIBE
• Missing semicolon at the end

3. Consider this sequence of commands in two separate sessions:

Session 1:
LISTEN channel1;
Session 2:
NOTIFY channel1, 'hello';

What will Session 1 receive?

medium

A. No message because LISTEN does not receive payloads

B. An error because NOTIFY cannot send messages

C. A notification on channel1 with payload 'hello'

D. A notification on a different channel

Solution

1. Step 1: Understand LISTEN and NOTIFY interaction

   Session 1 listens on channel1, so it waits for notifications on that channel.

2. Step 2: Analyze NOTIFY command

   Session 2 sends a notification on channel1 with payload 'hello'. This message is delivered to all listeners on channel1.

3. Final Answer:

   A notification on channel1 with payload 'hello' -> Option C

4. Quick Check:

   NOTIFY sends payload to LISTENers [OK]

Hint: NOTIFY sends payload to all LISTENers on channel [OK]

Common Mistakes:

• Thinking LISTEN ignores payloads
• Believing NOTIFY causes errors
• Assuming messages go to wrong channels

4. You run the following command:

NOTIFY 'channel1';

What is the problem with this command?

medium

A. The command is correct and will work.

B. NOTIFY requires a payload string.

C. NOTIFY cannot be used without LISTEN first.

D. Channel name must not be in quotes.

Solution

1. Step 1: Check NOTIFY syntax

   The syntax is NOTIFY channel [ , payload ]; channel can be an unquoted identifier or a string literal.

2. Step 2: Verify the command

   NOTIFY 'channel1'; is valid and notifies channel 'channel1' with no payload.

3. Final Answer:

   The command is correct and will work. -> Option A

4. Quick Check:

   NOTIFY accepts string literals for channel [OK]

Hint: NOTIFY channel names can be quoted strings or identifiers [OK]

Common Mistakes:

• Thinking NOTIFY requires a payload
• Believing NOTIFY needs LISTEN first
• Thinking channel names must not be quoted

5. You want to notify multiple listeners on different channels chan1 and chan2 with the same message 'update'. Which approach correctly sends notifications to both channels?

hard

A. Run NOTIFY chan1, 'update'; NOTIFY chan2, 'update'; in one transaction.

B. Run NOTIFY chan1, 'update' || chan2; to combine channels.

C. Run LISTEN chan1, chan2; then NOTIFY once.

D. Run NOTIFY 'chan1,chan2', 'update'; to notify both.

Solution

1. Step 1: Understand NOTIFY behavior

   NOTIFY sends a message to one channel at a time; it cannot notify multiple channels in one command.

2. Step 2: Analyze options

   Run NOTIFY chan1, 'update'; NOTIFY chan2, 'update'; in one transaction. runs two NOTIFY commands, one per channel, correctly notifying both. Options A, B, and D misuse syntax or commands.

3. Final Answer:

   Run NOTIFY chan1, 'update'; NOTIFY chan2, 'update'; in one transaction. -> Option A

4. Quick Check:

   One NOTIFY per channel needed [OK]

Hint: Notify each channel separately with NOTIFY [OK]

Common Mistakes:

• Trying to notify multiple channels in one NOTIFY
• Using LISTEN to send messages
• Quoting multiple channels as one string