Postgres Pro
Downloads
Home   >   mailing lists

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

From Sean Davis
Subject Re: Select and update with limit and locking
Date
Msg-id AANLkTi=J+24V2Yg7AUWoeO7G7Hzumgd3DS1W6puDKT-M@mail.gmail.com
Whole thread Raw
In response to Select and update with limit and locking  (matt w <ogeoon@gmail.com>)
Responses Re: Select and update with limit and locking  (Matt Wescott <mattwescott@gmail.com>)
List pgsql-novice
Tree view


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

pgsql-novice by date:

Previous
From: matt w
Date:
Subject: Select and update with limit and locking
Next
From: A B
Date:
Subject: Unique constraint on only some of the rows