// One problem with using PostgreSQL in this way (using either advisory locks or LOCK FOR UPDATE) is that it requires you to keep an open connection to the database whilst the job is being worked on.
//
Not necessarily. You lock the row for an instant update to a field, for example called "status" into "running" and then disconnect from the database within milliseconds. Finish your job taking as much time as you want. And then connect again to change the row's status to "finished".
This is how it's always designed, as I have seen. The locking problem is for querying rows where status="waiting" and then instantly change it.
It's not to "keep the record locked, and DB connection up, until I finish my batch job". That would be a bad design.
Not necessarily. You lock the row for an instant update to a field, for example called "status" into "running" and then disconnect from the database within milliseconds. Finish your job taking as much time as you want. And then connect again to change the row's status to "finished".
This is how it's always designed, as I have seen. The locking problem is for querying rows where status="waiting" and then instantly change it.
It's not to "keep the record locked, and DB connection up, until I finish my batch job". That would be a bad design.