Re: Select and update with limit and locking

On Sun, Jan 23, 2011 at 9:54 PM, matt w <ogeoon(at)gmail(dot)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:

http://www.postgresql.org/docs/9.0/static/tutorial-transactions.html

and

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE

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