I built something similar (by accident)

[adriangb]

Mostly as a learning exercise I built a project that turned out to be very similar: https://github.com/adriangb/pgmq

I had actually ⭐️ procrastinate a couple of years ago it seems but didn’t remember when I did a cursory search before writing pgmq. It was mostly a learning exercise anyway so I don’t regret writing it, but since it was written in isolation I’m curious what folks here think. Are there any obvious pitfalls that you’ve dealt with in procrastinate that I missed?

I think the hardest part for me was scope: Postgres is super powerful and you could tack on features forever. My first iteration had topics and subscriptions, I was planning on building Pub/Sub in Postgres. It’s totally possible, but I decided to cut back to keep things cleaner/simpler. Same thing with a couple other features like DLQs.

[ewjoachim]

Hey :) That's nice !

I'm not sure I'll have a lot of time to explore your project, but you're very welcome if there are things you'd like to bring over to Procrastinate !

[adriangb]

I think maybe the most interesting thing I put in pgmq that procrastinate doesn't seem to have is completion notifications. I made it work by with a function something like Queue.wait_for_completion(*ids: UUID) -> AsyncContextManager[CompletionHandle] where CompletionHandle is basically Awaitable[None]. I think the interesting bit is that when you publish a message you actually get back a CompletionHandle: Queue.send(body: bytes) -> AsyncContextManager[CompletionHandle]. This is important because it allows pgmq to start listening for completion before publishing the job, which eliminates race conditions. Completion events are handled much like new job events: I register a bunch of anyio.Events and wake them up whenever the corresponding job completion NOTIFY event is received. Just like with new job notifications, if no NOTIFY event is received after X seconds I do a poll.

I also (think) I was able to avoid orphaned jobs (jobs that were picked up by a worker which then died without being able to mark the job as failed or completed) by using an ack timeout mechanism (like used by SQS or Pub/Sub). When a worker "checks out" a job the available_at timestamp for that job is moved forward a couple of seconds and the job id is added to a list of "checked out jobs" which a background task periodically keeps alive by extending available_at a couple seconds further. If the worker dies then available_at stops getting extended and the job can be picked up by another worker. Thus there is no concept of an "in progress" job, it's just available or not. Once a job completes I just delete it from the table (keeping around completed jobs would be easy, I just didn't add it as a feature).

[ewjoachim]

Regarding the notify: That makes me think about #370

That could allow us to have at least half of the ticket (knowing when something finishes). We wouldn't have the result but that could be another step in that direction.

Regarding the "available_at"... Not sure. Especially because tasks might be sync, so it's possible that all our workers are busy handling tasks for minutes or even hours.

If procrastinate was purely async, it would unconditionnaly say it's a good idea.

(Yeah I'm still regretting this decision ^^)