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.

Search Discussions

  • Sean Davis at Jan 24, 2011 at 9:53 am

    On Sun, Jan 23, 2011 at 9:54 PM, matt w 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
  • Matt Wescott at Jan 24, 2011 at 6:39 pm
    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 wrote:


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 24, '11 at 2:54a
activeJan 24, '11 at 6:39p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase