3 ms·

Hmmm has anyone had problems with notify/listen and pg. IIUC transactions aren't finished until all listeners have acked the notifications, so if you have a/som

Hmmm has anyone had problems with notify/listen and pg. IIUC transactions aren't finished until all listeners have acked the notifications, so if you have a/some misbehaving listener/s you're in for strange undebuggable problems? I know there are things with 0mq or I guess kafka plugs, but I was wondering about real world experience.

sbuttgereit 6y ago
That's correct. According to the documentation the waiting notifications are queued until ACKed. If the queue is full, then new transactions can start to fail when they try to add to the queue via NOTIFY. Also, certain in-progress transactions can prevent cleanup of the queue so a long transaction could lead to it getting full. Per the documentation(https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html): "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. 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 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." [edit: I previously said that you weren't quite correct... but I originally misread what you said, I've updated my comment to say you are correct.]
- touisteur 6y ago
  Hey thanks for taking the time to 1) answer (trying to teach) and 2) coming back to correct your answer and cite detailed explanations. Wish I could upvote more than once sometimes. I was thrilled when I learned - on this here site - about listen/notify, and then went on reading the docs and the code (pg's code is surprising easy to read when you know what you're looking for - in fact it gave me enough confidence to build my own extensions...) and felt a bit underwhelmed. Hopefully someone upgrades the whole listen/notify one day for pluggability.
Diggsey 6y ago
I don't think that's correct - this would only happen if the notification queue was already full. Assuming the notification queue is not full then the original transaction does not wait for the notification to be ACKed. You can avoid most problems with listeners by having a single dedicated listener connection per application process (so all requests handled by that process multiplex onto the same listener). Since this connection only listens for notifications and never enters a transaction, it would be very difficult to fill up the notification queue. The queries themselves are executed on a separate connection pool, so that connections are never shared between the listener and other operations.
e1g 6y ago
Another "gotcha" with PG notify is that in PG the payload cannot be more than 8kb long. If the UPDATE query changes thousands of rows, it's quite easy to end up with a generated text longer than 8000bytes. I'm not sure if this error raised by NOTIFY would block the UPDATE from executing, but would be worth testing.

sbuttgereit 6y ago

That's correct. According to the documentation the waiting notifications are queued until ACKed. If the queue is full, then new transactions can start to fail when they try to add to the queue via NOTIFY. Also, certain in-progress transactions can prevent cleanup of the queue so a long transaction could lead to it getting full. Per the documentation(https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html): "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. 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 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." [edit: I previously said that you weren't quite correct... but I originally misread what you said, I've updated my comment to say you are correct.]

touisteur 6y ago
Hey thanks for taking the time to 1) answer (trying to teach) and 2) coming back to correct your answer and cite detailed explanations. Wish I could upvote more than once sometimes. I was thrilled when I learned - on this here site - about listen/notify, and then went on reading the docs and the code (pg's code is surprising easy to read when you know what you're looking for - in fact it gave me enough confidence to build my own extensions...) and felt a bit underwhelmed. Hopefully someone upgrades the whole listen/notify one day for pluggability.

Diggsey 6y ago

I don't think that's correct - this would only happen if the notification queue was already full. Assuming the notification queue is not full then the original transaction does not wait for the notification to be ACKed. You can avoid most problems with listeners by having a single dedicated listener connection per application process (so all requests handled by that process multiplex onto the same listener). Since this connection only listens for notifications and never enters a transaction, it would be very difficult to fill up the notification queue. The queries themselves are executed on a separate connection pool, so that connections are never shared between the listener and other operations.

e1g 6y ago

Another "gotcha" with PG notify is that in PG the payload cannot be more than 8kb long. If the UPDATE query changes thousands of rows, it's quite easy to end up with a generated text longer than 8000bytes. I'm not sure if this error raised by NOTIFY would block the UPDATE from executing, but would be worth testing.