zlacker

This assumes that you're creating a transaction per message, which I think is not advisable.

>>static+(OP)
if you care to elaborate, i'm curious -- what alternative(s) would you recommend instead of one transaction per message, and why?

>>shoo+t8
The article's approach is to have a column that stores if he job has been claimed and then handles dead jobs by just having a timeout.

>>static+(OP)
You can also create transactions for batches of messages if you want. Same as acking batches of messages in a queue system.

>>static+(OP)
Postgres implicitly creates a transaction for any query modifying data outside of one.

Transactions in MVCC are relatively cheap. The main resource of contention is a global txid that can disastrously wrap around if autovacuum is disabled. That process is responsible for a few other important tasks, like updating statistics for the query planner and maintaining BRIN indexes.

>>mdavid+HI
> Postgres implicitly creates a transaction for any query modifying data outside of one.

I should clarify. I meant holding a transaction for the duration of the message.

>>shoo+t8
What if you want to store an error message+status with the job if it fails? It means you have to commit something no? What if you want to mark the message as being worked on and what process locked it and when they did for some kind of job queue monitoring stats?

The times I have done this, I end up with a workflow where the "select for update ... skipped lock" is used only to initially mark the job as "taken" so that other processes do not start working on it. That update is committed in one transaction and then the "work" is done in second transaction.