Re: Select and update with limit and locking - Mailing list pgsql-novice

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:

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