What happens with a NOTIFY when no session has issued LISTEN in PostgreSQL?

Question

PostgreSQL has a good listen/notify system. Documentation says:

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 NOTIFY will fail at commit.

But I can't find out what happening with events in a specified channel that doesn't have listeners. Will notification queue overflow or will PG drop these events from queue?

Beirut

So basically a tree can't fall in a forest if nobody is around? – Odessaodetta

Sorry, but I can't understand what did you said. Is my question incorrect? – Beirut

It's a valid question, I was just trying to make sense of your observation. It's a riff on the "If a tree falls in a forest, does it make a sound" concept. It sounds like in Postgres land you can't push new events if there's no listeners. Redis, by contrast, just throws your notifications in the garbage since nobody cares to listen. – Odessaodetta

well if there are no listeners, then each message has been processed by all listeners. – Mcauliffe

PostgreSQL won't do anything in this case as @IfLoop suggested. You can try it out: NOTIFY non_existing_channel; – Rotatory

Oh, I understood about the tree, the answer depends on "sound" definition. Does PG think that a tree makes a sound when falls? :) – Beirut

@IfLoop, how it is possible? listener receive messages only for its channel. If You call NOTIFY non_existing_channel; nothing happens in other channels. – Beirut

Well, If i'm taking a coffee break and ask everyone in an empty room if they want some coffee, how long do I have to wait before I've gotten an answer from everyone in the room?. "All listeners" is not the same as "At least one listener", – Mcauliffe

Please include a link to the source of your quote in the question. That's how it's done. – Incorruptible

@Erwin Brandstetter, I did. Sorry for my mistake – Beirut

It's best to link to the current manual, unless a specific version is the focus. Compare meta.stackexchange.com/questions/108714/… – Incorruptible

Answer 1

It could be more clear in the manual, but there is definitive indication that the queue is cleaned as soon as no session is actively waiting for the notification. Per documentation:

However, no cleanup can take place if a session executes LISTEN 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.

That means, if nobody is listening (no active session has issued a LISTEN command on the same channel), nothing keeps Postgres from cleaning the queue instantly.

Incorruptible

Is there any way around this disadvantage? I don't want to lose any notifications if no process is listening at the moment but might in the future? Can we preserve the notifications which weren't being listened to? – Michelemichelina

@Zoran777: You might write to a log table in addition to NOTIFY. Each session only receives notifications issued after it has registered for a channel with LISTEN. Since pg 9.2 there is also no way to get a "list of listeners". See here and here There are other fine points. You might start a new question with details. You can always link to this one for context. – Incorruptible

Answer 2

When a transaction that has issued NOTIFY commits, the SQL engine looks up other sessions that LISTEN at this point in time for this notification.

If there is none, the notification is thrown away. It's not queued anywhere.

It's not explained directly like that in LISTEN documentation, but as noted by @IfLoop comments, it's implied by a strict interpretation of:

...a queue that holds notifications that have been sent but not yet processed by all listening sessions.

Not yet processed by zero session means there's nothing to enqueue.

Also it makes sense because otherwise each notifier should have to worry whether there are listeners or not, which would seriously hamper the usefulness of NOTIFY.

Pend

Even worse. What if there is a listener, but there should be two? The sender can't know. The sender can only expect that many receivers, that it is aware of. If there is none, than the message can get discarded right away. – Sherly