Обсуждение: Select and update with limit and locking
I'm trying to implement a priority queue. Performance is not much of an issue but there will be about 10m entries.
I need to lock the table, select ~1000 entries (out of ~1m matching the query) and update fields on only the entries selected.
Thanks so much for any help you can give me.
On Sun, Jan 23, 2011 at 9:54 PM, matt w <ogeoon@gmail.com> wrote:
I'm trying to implement a priority queue. Performance is not much of an issue but there will be about 10m entries.I need to lock the table, select ~1000 entries (out of ~1m matching the query) and update fields on only the entries selected.Thanks so much for any help you can give me.
Hi, Matt.
Have a look at the following sections of the manual:
and
The basic idea is to start a transaction, select for update, do the update, and then commit the transaction. The select for update will do the "locking". Of course, the table should be indexed appropriately, etc.
Sean
But I need to use LIMIT, which can't be used with UPDATE, right? It seems like I need to use ctid somehow, but I'm not finding much information on how to do that.
On Mon, Jan 24, 2011 at 1:52 AM, Sean Davis <sdavis2@mail.nih.gov> wrote:
On Sun, Jan 23, 2011 at 9:54 PM, matt w <ogeoon@gmail.com> wrote:I'm trying to implement a priority queue. Performance is not much of an issue but there will be about 10m entries.I need to lock the table, select ~1000 entries (out of ~1m matching the query) and update fields on only the entries selected.Thanks so much for any help you can give me.Hi, Matt.Have a look at the following sections of the manual:andThe basic idea is to start a transaction, select for update, do the update, and then commit the transaction. The select for update will do the "locking". Of course, the table should be indexed appropriately, etc.Sean