I tested it by visual inspection of advisory locks in pg_locks; once with a small test table, and once on a larger
'operations'table in our test environment. It seemed to work, but I hear you, I don't like to depend on the mood of
theoptimizer. The drawback of the subquery version is that if the queue table gets big (eg: consumers are shut off) it
willspend a lot of time building a worktable only to get a few items from it later in the outer query. Perhaps putting
alimit in the inner query as well can alleviate that, like: LIMIT (expected number of consumers) * (batch size)
There is a theoretical race condition with this approach (fast vs slow consumers) but it's harmless - a consumer that
executesthe inner query slowly may get less or no items to process, although there might be some, but they will picked
upeventually, so it's no big deal.
thanks,
Kiriakos
On Feb 16, 2012, at 12:44 AM, Tom Lane wrote:
> Kiriakos Georgiou <kg.postgresql@olympiakos.com> writes:
>> As I understand it the order the of evaluation of search arguments is up to the optimizer. I've tested the
followingquery, that is supposed to take advantage of advisory locks to skip over rows that are locked by other
consumersrunning the exact same query and it seems to work fine. It seems to me the optimizer should always prefer to
scanby status. What say you?
>
> "When it breaks, you get to keep both pieces." Was your testing even
> capable of noticing the problem if the query locked more rows than you
> wanted?
>
> Less dangerous coding practices might involve putting the lock function
> in an outer query, while using an OFFSET 0 in the sub-query as an
> optimization fence.
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general