3 ms·

Here's how it works with JOINS in Postgres, using the NOTIFY/LISTEN mechanism -- Run the query and store result in updated_ids WITH updated_ids AS ( UPD

Here's how it works with JOINS in Postgres, using the NOTIFY/LISTEN mechanism -- Run the query and store result in updated_ids WITH updated_ids AS ( UPDATE public.doc SET version = version + 1 -- Joins are not directly part of Postgres' update syntax, but this achieves the same FROM public.org WHERE public.org.id = public.doc.org_id AND public.org.type = 'customer' -- Returns all ids of affected rows RETURNING public.doc.id ) -- convert the returned ids into a comma-separated string that we can sent via the NOTIFY mechanism , id_string_result AS ( SELECT ARRAY_TO_STRING(ARRAY_AGG(id) ,',') AS id_string FROM updated_ids ) -- use LISTEN/NOTIFY to publish a changed_row_ids event with a comma separated list of changed row ids SELECT pg_notify( 'changed_row_ids', id_string_result.id_string )

touisteur 6y ago
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 5y 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 5y 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.

touisteur 6y ago

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 5y 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 5y 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.