FOR UPDATE cannot be applied to the nullable side of an outer join #714
Description
Since Postgresql doesn't support FOR UPDATE in nullable side of outer join (see here), caching access token after introspection throws this error on master branch:
Postgresql:
ERROR: FOR UPDATE cannot be applied to the nullable side of an outer join
STATEMENT: SELECT "oauth2_provider_accesstoken"."id", "oauth2_provider_accesstoken"."user_id", "oauth2_provider_accesstoken"."source_refresh_token_id", "oauth2_provider_accesstoken"."token", "oauth2_provider_accesstoken"."application_id", "oauth2_provider_accesstoken"."expires", "oauth2_provider_accesstoken"."scope", "oauth2_provider_accessto ken"."created", "oauth2_provider_accesstoken"."updated", "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_ name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined", "oauth2_provider_application"."id", "oauth2_provider_application"." client_id", "oauth2_provider_application"."user_id", "oauth2_provider_application"."redirect_uris", "oauth2_provider_application"."client_type", "oauth2_provider_application". "authorization_grant_ type", "oauth2_provider_application"."client_secret", "oauth2_provider_application"."name", "oauth2_provider_application"."skip_authorization", "oauth2_provider_application"."created", "oauth2_pro vider_application"."updated" FROM "oauth2_provider_accesstoken" LEFT OUTER JOIN "auth_user" ON ("oauth2_provider_accesstoken"."user_id" = "auth_user"."id") LEFT OUTER JOIN "oauth2_provider_application" ON ("oauth2_provider_accesstoken"."application_id" = "oauth2_provider_application"."id") WHERE "oauth2_provider_accesstoken"."token" = 'TOKEN' FOR UPDATEDjango:
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
response = get_response(request)
File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py", line 126, in _get_response
response = self.process_exception_by_middleware(e, request)
File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py", line 124, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/usr/local/lib/python3.7/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
return view_func(*args, **kwargs)
File "/usr/local/lib/python3.7/site-packages/django/views/generic/base.py", line 68, in view
return self.dispatch(request, *args, **kwargs)
File "/usr/local/lib/python3.7/site-packages/rest_framework/views.py", line 495, in dispatch
response = self.handle_exception(exc)
File "/usr/local/lib/python3.7/site-packages/rest_framework/views.py", line 455, in handle_exception
self.raise_uncaught_exception(exc)
File "/usr/local/lib/python3.7/site-packages/rest_framework/views.py", line 483, in dispatch
self.initial(request, *args, **kwargs)
File "/usr/local/lib/python3.7/site-packages/rest_framework/views.py", line 400, in initial
self.perform_authentication(request)
File "/usr/local/lib/python3.7/site-packages/rest_framework/views.py", line 326, in perform_authentication
request.user
File "/usr/local/lib/python3.7/site-packages/rest_framework/request.py", line 223, in user
self._authenticate()
File "/usr/local/lib/python3.7/site-packages/rest_framework/request.py", line 376, in _authenticate
user_auth_tuple = authenticator.authenticate(self)
File "/usr/local/lib/python3.7/site-packages/oauth2_provider/contrib/rest_framework/authentication.py", line 29, in authenticate
valid, r = oauthlib_core.verify_request(request, scopes=[])
File "/usr/local/lib/python3.7/site-packages/oauth2_provider/oauth2_backends.py", line 166, in verify_request
valid, r = self.server.verify_request(uri, http_method, body, headers, scopes=scopes)
File "/usr/local/lib/python3.7/site-packages/oauthlib/oauth2/rfc6749/endpoints/base.py", line 87, in wrapper
return f(endpoint, uri, *args, **kwargs)
File "/usr/local/lib/python3.7/site-packages/oauthlib/oauth2/rfc6749/endpoints/resource.py", line 75, in verify_request
return token_type_handler.validate_request(request), request
File "/usr/local/lib/python3.7/site-packages/oauthlib/oauth2/rfc6749/tokens.py", line 351, in validate_request
token, request.scopes, request)
File "/usr/local/lib/python3.7/site-packages/oauth2_provider/oauth2_validators.py", line 370, in validate_bearer_token
introspection_credentials
File "/usr/local/lib/python3.7/site-packages/oauth2_provider/oauth2_validators.py", line 342, in _get_token_from_authentication_server
"expires": expires,
File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 502, in update_or_create
obj = self.select_for_update().get(**lookup)
File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 393, in get
num = len(clone)
File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 250, in __len__
self._fetch_all()
File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 1186, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/usr/local/lib/python3.7/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 "/usr/local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1065, in execute_sql
cursor.execute(sql, params)
File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 100, in execute
return super().execute(sql, params)
File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python3.7/site-packages/django/db/utils.py", line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/usr/local/lib/python3.7/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 joinRemoving select_related fixes the issue.