Thread: Postgresql the right tool (queue using advisory_locks + long transactions)

Dorian Hoxha <dorian.hoxha@gmail.com> writes:
> 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.

>    1. 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).

Sitting on an open transaction for hours would be a bad idea.  An idle
session isn't a problem though.  So as long as you use session locks not
transaction locks, this should work fine.

>    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 ?

There's no such thing in core postgres.  Perhaps pgbouncer or another
connection pooler has such a feature; though I'm not sure if a pooler
might not cause problems of its own (it probably won't realize that the
connections aren't interchangeable if they're holding session-level
advisory locks).  I'm a bit confused though as to why you'd want this for
your application.  Have you got an a-priori hard limit as to how long your
"long jobs" could take?  Do you really want to kill their database
connections if they take a bit longer than you thought?  Seems like as
long as the client stays connected, it'd be better to assume it's still
working.

            regards, tom lane

Dorian Hoxha wrote
> Hi list,
>
> 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.
>
>
>    1. 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).
>    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 ?

Do you understand the difference between a session and a transaction?  Your
subject and body indicate either you don't or have a thinko/typo somewhere?
Long-running transactions (idle or otherwise) are a major problem while
long-running idle sessions are simply marginally wasteful (in moderation).

Why not just update the job as dispatched when it is being worked on and
then completed when finished?  You still would need to handle partial
completion somehow but this way you don't waste live resources during the
long waiting period simply monitoring a lock.  Though probably at your
volume this is not that big an issue.

PostgreSQL proper does not have session timeouts that I am aware of.  If a
worker fails it should release its connection and all advisory locks would
be released...

You reference to a connection pooler in the above doesn't make sense to me;
you'd need persistent connections for this to work (they can be from a pool
but the pool size would have to be 100+).

The main decision is whether job process state is part of your data model or
simply an artifact.  I'd suggest that it should be part of the model so
state should be managed directly thorough row-level locking and status
fields instead of indirectly through temporary system advisory locks.

If you want to keep track of active workers you should setup some kind of
heartbeat update query; and maybe depending on how important this is attach
advisory lock info to that heartbeat record so a monitoring process can
check both the pulse table and the system advisory lock for a two data point
confirmation of activity.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgresql-the-right-tool-queue-using-advisory-locks-long-transactions-tp5801667p5801670.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi,


On 2014-04-27 21:53, Tom Lane wrote:
 >
> Sitting on an open transaction for hours would be a bad idea.

I'm wondering why this is and what the consequences might be - I
thought, the MVCC model would handle that rather well?

Could please someone elaborate on this or provide some pointer? Thanks!


Best regards,

    -hannes

On 4/27/2014 2:07 PM, Hannes Erven wrote:
> On 2014-04-27 21:53, Tom Lane wrote:
> >
>> Sitting on an open transaction for hours would be a bad idea.
>
> I'm wondering why this is and what the consequences might be - I
> thought, the MVCC model would handle that rather well?
>
> Could please someone elaborate on this or provide some pointer? Thanks!

vacuum can't free up any tuples older than the oldest transaction in the
database.  also, indexes need to index all versions of a tuple that
could still be in use    in a heavy update environment, this could lead
to quite a lot of database bloat.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Hannes Erven <hannes@erven.at> writes:
> On 2014-04-27 21:53, Tom Lane wrote:
>> Sitting on an open transaction for hours would be a bad idea.

> I'm wondering why this is and what the consequences might be - I
> thought, the MVCC model would handle that rather well?

Vacuum can't delete dead rows if there's some transaction that can still
see them.  So long-running transactions + update activity = table bloat.

(I had the idea that we'd fixed this, in recent releases, if you're using
READ COMMITTED isolation level; but some experimentation says VACUUM still
won't release rows while there are idle transactions.)

            regards, tom lane