django.db.utils.NotSupportedError: FOR UPDATE cannot be applied to the nullable side of an outer join

Question

I've found this error in server log. Can't replicate the problem.

  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/models/query.py", line 250, in __len__
    self._fetch_all()
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/models/query.py", line 1186, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/models/query.py", line 54, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1065, in execute_sql
    cursor.execute(sql, params)
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/futilestudio/.venvs/36venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.NotSupportedError: FOR UPDATE cannot be applied to the nullable side of an outer join

I think that it happens only on PostgreSQL database. I tried Sqlite before and it worked.

The problem is here:

match, created = Match.objects.update_or_create(
    match_id=draft_match.match_id,
    defaults={
        field.name: getattr(
            temp_match, field.name, None
        ) for field in Match._meta.fields if not field.name in [
            'id', 'pk'
        ]
    }
)

Is there a problem in attributes or do I must not use update_or_create and do it another way?

EDIT

In [18]: match, created = Match.objects.update_or_create(match_id=draft_match.match_id, defaults={
    ...:                 field.name: getattr(temp_match, field.name, None) for field in Match._meta.fields if
    ...:                 not field.name in ['id', 'pk','created','modified','home_team','away_team']})

returns the same error so I checked the defaults and NONE of them is ReversedForeignKey, there are only two ForeignKey but when I exclude them, it raises the same error.

In [22]: {
    ...:     ...:                 field.name: (getattr(temp_match, field.name, None),field) for field in Match._meta.fields if
    ...:     ...:                 not field.name in ['id', 'pk','created','modified']}
    ...:                 
    ...:                 
Out[22]: 
{'match_url': ('https://cestohlad.eu/sport-kansas-city-monterrey/',
  <django.db.models.fields.URLField: match_url>),
 'match_id': ('4ps3utZN', <django.db.models.fields.CharField: match_id>),
 'datetime': (datetime.datetime(2019, 4, 12, 3, 0),
  <django.db.models.fields.DateTimeField: datetime>),
 'home_team': (<Team: Sporting Kansas City (USA) (CURGfJWt)>,
  <django.db.models.fields.related.ForeignKey: home_team>),
 'away_team': (<Team: Monterrey (Mex) (Ya23C2Zs)>,
  <django.db.models.fields.related.ForeignKey: away_team>),
 'home_score': (2,
  <django.db.models.fields.PositiveSmallIntegerField: home_score>),
 'away_score': (5,
  <django.db.models.fields.PositiveSmallIntegerField: away_score>),
 'home_odds': (Decimal('0.6215'),
  <django.db.models.fields.DecimalField: home_odds>),
 'away_odds': (Decimal('0.4850'),
  <django.db.models.fields.DecimalField: away_odds>),
 'under_odds': (Decimal('2.02'),
  <django.db.models.fields.DecimalField: under_odds>),
 'over_odds': (Decimal('1.84'),
  <django.db.models.fields.DecimalField: over_odds>),
 'total': (Decimal('2.75'), <django.db.models.fields.DecimalField: total>),
 'total_real': (Decimal('7.00'),
  <django.db.models.fields.DecimalField: total_real>),
 'correct': (False, <django.db.models.fields.BooleanField: correct>),
 'home_odds_raw': (Decimal('0.4464'),
  <django.db.models.fields.DecimalField: home_odds_raw>),
 'draw_odds_raw': (Decimal('0.2817'),
  <django.db.models.fields.DecimalField: draw_odds_raw>),
 'away_odds_raw': (Decimal('0.3484'),
  <django.db.models.fields.DecimalField: away_odds_raw>)}

Answer 1

I got this message because the the get_queryset() method has been overridden in the model manager. This method is also called by update_or_create.

def get_queryset(self):
    return super().get_queryset().annotations()

After getting rid of it, everything worked fine again. .annotations() was executing some group_by clause. That's why update_or_create couldn't proceed.