Currently I am trying to delete a row(s) in a particular database table api_user. Yet deleting hangs for seemingly infinite time ( currently been running for 1800 seconds as I've been looking for answers ). The row in question had foreign key dependents, yet all of those dependents were deleted already, thats been verified.
I'm running all of my database introspection through Postico ( just another database GUI client ) So When i cancel the query i receive this error message.
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."api_event" x WHERE $1::pg_catalog.text OPERATOR(pg_catalog.= ) "user_id"::pg_catalog.text FOR KEY SHARE OF x"
There are indexes that referenced the rows in this table. api_event is one table that had indexes on and foreign keys to this table. All of the dependent rows from api_event were deleted.
I've checked pg_stat_activity for any queries that could be running concurrently to no avail, and so I'm at a point where I'm not sure what the next question I should be asking is. Any direction would be great!
Running EXPLAIN DELETE FROM api_user WHERE organization_id = '<replaced value>'; returns this to me:
Delete on api_user (cost=54.94..2903.50 rows=1874 width=6)
-> Bitmap Heap Scan on api_user (cost=54.94..2903.50 rows=1874 width=6)
Recheck Cond: ((organization_id)::text = '<replaced value>'::text)
-> Bitmap Index Scan on api_user_organization_id (cost=0.00..54.47 rows=1874 width=0)
Index Cond: ((organization_id)::text = '<replaced value>'::text)
Lock Monitoring
Did you check if it's waiting for a lock? – a_horse_with_no_name
As per request I searched the locks on my database. I used this query:
select t.relname,
l.locktype,
page,
virtualtransaction,
pid,
mode,
granted
from pg_locks l,
pg_stat_all_tables t
where l.relation=t.relid
order by relation asc;
The first return, my DELETE was not running, container 3 rows of locks from pg_class, pg_index, and pg_namespace.
The second return, my DELETE was running, contained 21 rows of locks. All of which were of a relname from a previously deleted set of rows that had either a foreign key or an index with this row.
Road to Resolution
Through more questions and researching, an interesting tidbit arose that not all foreign keys on child tables have indexes. After composing a query to see what foreign keys don't have indexes I noted that api_event did not have an index to its api_user foreign key. Now api_event is a humongous table.
Creating an index on api_event solved the issue.
CREATE INDEX CONCURRENTLY user_id_to_events ON api_event(user_id);