Skip site navigation (1) Skip section navigation (2)

Re: SKIP LOCKED DATA

From: Thomas Munro <munro(at)ip9(dot)org>
To: Ilya Kosmodemiansky <hydrobiont(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: SKIP LOCKED DATA
Date: 2012-01-17 21:55:30
Message-ID: CADLWmXVdjU9YjsvHt25T2MBoe-r6RjvCUr58kpg=FnPFm_dHkQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-01-17 21:57:05
Subject: Re: Command Triggers
Previous:From: Dimitri FontaineDate: 2012-01-17 21:43:48
Subject: Re: Command Triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group