From: | Matt Wescott <mattwescott(at)gmail(dot)com> |
---|---|
To: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> |
Cc: | matt w <ogeoon(at)gmail(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Select and update with limit and locking |
Date: | 2011-01-24 18:39:02 |
Message-ID: | AANLkTinS0QVcSwcZc0EK3SCuxR4dXK7F8eb-d-_kKUnr@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bastiaan Olij | 2011-01-25 00:00:38 | Controlling pgAgent |
Previous Message | Chris Campbell | 2011-01-24 18:35:54 | Re: Passing a variable from the user interface to PostgreSQL |