Use "NO KEY" when doing select_for_update for PostgreSQL

[Manuel Weitzman]

Currently Django compiles select for update into one of

• FOR UPDATE
• FOR UPDATE NOWAIT
• FOR UPDATE SKIP LOCKED

All of these acquire a lock which conflicts with KEY SHARE locks. This means that updates on tables which references a row locked by FOR UPDATE will have to wait until the lock is released. This is to avoid any unexpected changes on PRIMARY KEY fields but, in Django, primary key fields are read-only, so locking them makes no sense.

There is no need to acquire these kind of locks, instead, we should (at least be able to) use

• FOR NO KEY UPDATE
• FOR NO KEY UPDATE NOWAIT
• FOR NO KEY UPDATE SKIP LOCKED

which can be easily done by overriding BaseOperation.for_update_sql in Postgres DatabaseOperation

For 1.11

def for_update_sql(self, nowait=False, skip_locked=False):
    """
    Returns the FOR UPDATE SQL clause to lock rows for an update operation.
    """
    if nowait:
        return 'FOR NO KEY UPDATE NOWAIT'
    elif skip_locked:
        return 'FOR NO KEY UPDATE SKIP LOCKED'
    else:
        return 'FOR NO KEY UPDATE'

For 2.2

def for_update_sql(self, nowait=False, skip_locked=False, of=()):
    """
    Return the FOR UPDATE SQL clause to lock rows for an update operation.
    """
    return 'FOR NO KEY UPDATE%s%s%s' % (
        ' OF %s' % ', '.join(of) if of else '',
        ' NOWAIT' if nowait else '',
        ' SKIP LOCKED' if skip_locked else '',
    )

Not doing so causes lock contention in our use case.