I am trying to use postgresql as a queue for long-jobs (max ~4 hours) using advisory_locks. I can't separate the long-job into sub-jobs.
At ultimate-best-case scenario there will be ~100 workers, so no web-scale performance required.
Is there a problem with 100 open sessions (behind a connection pooler?) for hours like in my case? The session will commit the progress of the job, but will stay opened and hold the advisory_lock till the job is done or it expires (look 2).
Is it possible to set a time limit to auto-close an opened session that hasn't made a query in a while ? So if a worker fails,and the session time-outs, postgresql/pgbouncer will close the session and release the lock ?
