Re: SKIP LOCKED DATA

On 16 January 2012 08:06, Ilya Kosmodemiansky <hydrobiont(at)gmail(dot)com> wrote:
> That is quite useful feature to implement smth. like message queues
> based on database and so on.
> Now there is possibility to jump over luck of such feature in Postgres
> using current advisory lock implementation (pg_try_advisory_xact_lock
> to determine if somebody already acquired log on particular row).
> So Im not sure this is an urgent matter.

Thanks Ilya. I knew about advisory locks but hadn't though of using
them like this. I tried some simple examples using SELECT ... FROM
... WHERE pg_try_advisory_xact_lock(id) [FOR UPDATE] LIMIT 1 in
transactions from a couple of different sessions and it achieves the
right effect. I could imagine that in theory there might be order of
evaluation subtleties in some cases where you have more things in your
WHERE clause though. I guess you want the pg_try_advisory_xact_lock
to be tested last after all other conditions are satisfied (ie for
minimal lock contention, avoiding false positives).

So I guess the question is whether it's worth implementing an explicit
feature to match other RDMBSs, complement NOWAIT and avoid theoretical
order of evaluation problems, or if this technique is enough.