How to deal with django migration postgres deadlock?

Question

So, I was deploying the django app to production and then the infamous postgres deadlock situation happened. This happens during the django migration.

Django version: 3.2 Postgres 13 Google cloud sql postgres.

OperationalError
deadlock detected
DETAIL:  Process 112506 waits for AccessExclusiveLock on relation 29425 of database 27145; blocked by process 112926.
Process 112926 waits for RowShareLock on relation 29381 of database 27145; blocked by process 112506.
HINT:  See server log for query details.

I ran this query to get the process info:

SELECT 29425::regclass,29381::regclass from pg_locks;

and result:

regclass | regclass
"custom_requestlog" "auth_user"
"custom_requestlog" "auth_user"

I am not sure how to proceed ahead, as the pgaudit has been enabled but it doesnt show anything and also the query insights is not that helpful. Attached is image of query insights.

Any help would be helpful please!

Update: The query from log explorer in Google cloud, gave this query just after deadlock detected error:

2022-04-29 13:51:36.590 UTC [6445]: [732-1] db=xyz_prod,user=backend-prod DETAIL:  Process 6445 waits for AccessExclusiveLock on relation 29425 of database 27145; blocked by process 9249.
Process 9249 waits for RowShareLock on relation 29381 of database 27145; blocked by process 6445.
Process 6445: SET CONSTRAINTS "custom_requestlog_user_id_3ff3f1cf_fk_some_user_id" IMMEDIATE; ALTER TABLE "custom_requestlog" DROP CONSTRAINT "custom_requestlog_user_id_3ff3f1cf_fk_some_user_id"
Process 9249: INSERT INTO "custom_requestlog" ("user_id", "ip_addr", "url", "session_key", "method", "headers", "query", "body", "cookies", "timestamp", "status_code", "response_snippet") VALUES (NULL, 'xx.xxx.xx.xxx'::inet, '/version/', NULL, 'GET', '{"HTTP_HOST": "api.some.com", "HTTP_ACCEPT": "*/*", "HTTP_ACCEPT_ENCODING": "deflate, gzip", "HTTP_USER_AGENT": "GoogleStackdriverMonitoring-UptimeChecks(https://cloud.google.com/monitoring)", "HTTP_X_CLOUD_TRACE_CONTEXT": "xxxxxx/9771676669485105781", "HTTP_VIA": "1.1 google", "HTTP_X_FORWARDED_FOR": "xx.xxx.xx.xxx, xx.xxx.xx.xxx", "HTTP_X_FORWARDED_PROTO": "https", "HTTP_CONNECTION": "Keep-Alive"}', '{}', '\x'::bytea, '{}', '2022-04-29T13:48:46.844830+00:00'::timestamptz, 200, NULL) RETURNING "custom_requestlog"."id"

Answer 1

Got a similar issue yesterday and here's how I dealt with it. Hopefully can help someone.

1. do a python management.py sqlmigrate APP MIGRATE_SEQUENCE to get the sql commands for that migration file
2. The SQL commands are in a big transaction (BEGIN/COMMIT block)
3. separate the commands into several smaller transactions. E.g, alter one table in each transaction
4. apply those transactions manually
5. do a python management.py migrate APP MIGRATE_SEQUENCE --fake to register the migration