Re: Select and update with limit and locking

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(at)mail(dot)nih(dot)gov> wrote:

>
>
> 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
>
>