Databases
StackOverflow2013
Postsdbo
POIncrement a counter and trigger an action when a threshold is exceeded
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIncrement a counter and trigger an action when a threshold is exceeded
    primarykey
    data
    text
    <p>I have a model like this</p> <pre class="lang-py prettyprint-override"><code>class Thingy(models.Model): # ... failures_count = models.IntegerField() </code></pre> <p>I have concurrent processes (Celery tasks) that need to do this:</p> <ol> <li>do some kind of processing</li> <li>if the processing fails increment <code>failures_counter</code> of the respective <code>Thingy</code></li> <li>if <code>failures_counter</code> exceeds a threshold for some <code>Thingy</code>, issue a warning, but ONLY ONE warning.</li> </ol> <p>I have some ideas about how to do this without a race condition, for example using explicit locks (via <code>select_for_update</code>):</p> <pre class="lang-py prettyprint-override"><code>@transaction.commit_on_success def report_failure(thingy_id): current, = (Thingy.objects .select_for_update() .filter(id=thingy_id) .values_list('failures_count'))[0] if current == THRESHOLD: issue_warning_for(thingy_id) Thingy.objects.filter(id=thingy_id).update( failures_count=F('failures_count') + 1 ) </code></pre> <p>Or by using Redis (it's already there) for synchronization:</p> <pre class="lang-py prettyprint-override"><code>@transaction.commit_on_success def report_failure(thingy_id): Thingy.objects.filter(id=thingy_id).update( failures_count=F('failures_count') + 1 ) value = Thingy.objects.get(id=thingy_id).only('failures_count').failures_count if value &gt;= THRESHOLD: if redis.incr('issued_warning_%s' % thingy_id) == 1: issue_warning_for(thingy_id) </code></pre> <p>Both solutions use locks. As I'm using PostgreSQL, is there a way to achieve this without locking?</p> <hr> <p>I'm editing the question to include the <strong>answer</strong> (thanks to Sean Vieira, see answer below). The question asked about a way to avoid locking and this answer is optimal in that it leverages <a href="http://www.postgresql.org/docs/9.2/static/mvcc-intro.html" rel="nofollow"><em>multi-version concurrency control</em> (MVCC) as implemented by PostgreSQL</a>.</p> <p>This specific question explicitly allowed using PostgreSQL features, and though many RDBMSs implement <code>UPDATE ... RETURNING</code>, it is not standard SQL and is not supported by Django's ORM out of the box, so it requires using raw SQL via <code>raw()</code>. The same SQL statement will work in other RDBMSs but every engine requires its own discussion regarding synchronization, transactions isolation and concurrency models (e.g. MySQL with MyISAM would still use locks).</p> <pre class="lang-py prettyprint-override"><code>def report_failure(thingy_id): with transaction.commit_on_success(): failure_count = Thingy.objects.raw(""" UPDATE Thingy SET failure_count = failure_count + 1 WHERE id = %s RETURNING failure_count; """, [thingy_id])[0].failure_count if failure_count == THRESHOLD: issue_warning_for(thingy_id) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDavide R.
    1. USDavide R.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIncrement a counter and trigger an action when a threshold is exceeded
      1. USDavide R.
      1. VTBountyStart
    2. VO
      singulars
      1. POIncrement a counter and trigger an action when a threshold is exceeded
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIncrement a counter and trigger an action when a threshold is exceeded
      1. This table or related slice is empty.
      1. VTUpMod
    1. COeasiest would be to have both counters in redis...
      singulars
      1. POIncrement a counter and trigger an action when a threshold is exceeded
      1. USarmonge
    2. CO@armonge that would be good for a different setup than the one I'm using. In my setup I need a long term storage for the failures count, while Redis is only used for cache/sync.
      singulars
      1. POIncrement a counter and trigger an action when a threshold is exceeded
      1. USDavide R.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload