zlacker

If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried.

Of course, the database client and server together form a distributed system. The client might continue processing a job under the mistaken impression that it still holds the lock. Jobs still need to be idempotent, as with the streaming platforms.

>>mdavid+(OP)
This assumes that you're creating a transaction per message, which I think is not advisable.

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

>>shoo+Ka
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+h2
You can also create transactions for batches of messages if you want. Same as acking batches of messages in a queue system.

>>static+h2
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+(OP)
> If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried.

Only if you begin a transaction when your job starts that isn’t committed until you job finishes. As I understand it, this is not a good idea for long-running jobs, since you’ll have a long-running Postgres transaction. Am I missing something? The linked article doesn’t seem to use this approach.

Does the “FOR UPDATE” decrease the lock to just a single row, thus making it unproblematic?

>>mdavid+YK
> 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+Ka
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.