Postgres Notify for Ecto-backed Queue?

[koudelka]

Postgres Notify for Ecto-backed Queue?

[koudelka]

Hey there, certainly. :)

If I'm understanding correctly, a modification to the current Ecto queue to turn off the poller, and add a listener for NOTIFY events would be sufficient? That is, when the queue hears a NOTIFY, it triggers a poll.

[jeremyjh]

Yes that is along the lines I have in mind. I guess an even simpler thing would be to have a way to disable the poller and a function to trigger a poll. But a NOTIFY based listener would fit well with this library I think.

[koudelka]

What do you think of a mode: :poll | :listen option in Honeydew.EctoPollQueue.child_spec/1 to turn off the poller and add a listener GenServer to the supervision tree?

There's at least once scenario that needs some consideration: When queues don't hear the NOTIFY, due to a network issue, or because there simply aren't any queues running.

• If there are no queues running, this scenario will resolve itself when a queue starts up, as workers ask for jobs when they start up.

• If there are queues running, and they didn't hear the NOTIFY because of a network issue, the next successful NOTIFY will dispatch jobs to all possible workers and remedy the issue. The caveat here is that you need at least a tiny bit of activity for this situation to resolve. The pathological error condition is if there are jobs waiting to run in the database, then the partition heals, but no more jobs are added (and hence no NOTIFY to kick things off), then none of the older jobs will be seen until a NOTIFY happens.

Leaving the poller on, but with a long interval would solve that, with that interval amount of latency. Or you could receive a reconnection event in the listener (somehow) and send a "reserve" message to the queue to get it going again. Honestly, I think leaving the poller on, but with, say, a minute interval, would be the safest solution. What do you think?

You shouldn't have to worry about overwhelming the queue with "reserve" messages, if there are no awaiting workers, it'll just drop them.

[jeremyjh]

Probably in most cases having a timer poll as a fallback would be a good idea. Maybe instead of tying NOTIFY and polling configuration together, honeydew could support a :never option for :poll_interval in case someone wants to disable polling.

NOTIFY should probably let the channel name be configurable.

As far as reconnect notices, when the connection used by a Postgrex.Notification listener dies, it should kill the notification process and the owner process that started it - typically a GenServer that is supervised so it will get restarted and try to reconnect and start listening again.

[koudelka]

I like your poll_interval: :never idea.

The channel name sounds like it'll definitely have to be configurable to fit your use case, it should probably default to the name of the schema otherwise.

It might be nice to provide another function that the user can optionally call from their application code to trigger a NOTIFY. I imagine normally you would want the notify to be inside of the schema's insert transaction, but I don't want to get involved in the user's domain code by providing them an Ecto.Multi with a pre-packed notify.

For reconnecting the listener, some care will have to be taken to ensure that when the database isn't contactable, it doesn't rapidly fail and overload the restart intensity of the supervision tree, and cause the entire application to fail.

[jeremyjh]

So I've been integrating Honeydew into our application and my thinking on this has changed a bit. Our system right now is efficient because the NOTIFY message sends a JSON payload that is automatically built by postgres's row_to_json(NEW), and the application gets that payload and processes it immediately - without loading anything from the database. I'd like to preserve that efficiency while relying on the Honeydew machinery to improve reliability (fallback to poll if no one was listening).

So my thinking now is that the trigger that inserts the row to the table and sends the NOTIFY, will also lock the record under the assumption that a notify listener will process the job - I use an advisory lock to ensure there is at most one listener at a time. When the Honeydew row lock expires the poll would catch it as a fallback. This also means my listener has to update the lock to -1 when it is done.

I'm beginning to think this is perhaps too specific to our use-case - or at least too complicated to justify inclusion for one user - while using NOTIFY as a poll trigger might be useful to more people.

[koudelka]

Yeah, it is a bit too specific.

I was aiming for the queue to be as hands off as possible, and not require any special interaction from the developer to "enqueue" jobs, and also to ensure that the state of the lock field is the authoritative the state of the queue.

I think your hybrid solution sounds good for your use case. I'm not sure that using NOTIFY as a poll trigger is worth the extra complexity, since there's already going to be a fallback poll in place, the worst case is that job latency is the poll interval. Is low latency critical for you?

You mentioned that your listener is going to set the lock to -1, that's "abandoned" to Honeydew, meaning that it failed to execute the job too many times. NULL indicates that the job was successful, or is otherwise not meant to be seen by Honeydew anymore.

[jeremyjh]

Yes latency is a concern - we started with NOTIFY for that reason and were looking more for a way to ensure the NOTIFY has a fallback - in case no one is listening - than for a traditional asynchronous job queue; but what I'd have to build as a fallback handler happens to be very much like an asynchronous job queue.

Thanks for the tip about the lock value. The most dodgy part of this hybrid plan is that I'm messing with the honeydew_lock in my trigger and application code.

[koudelka]

Feel free to drop me a line if you need anything. :)

[jeremyjh]

Thanks - closing this as I don't think anything is needed in Honeydew at the moment.