CODEWITHSUNDEEP
databasepostgresql
Darius Calliet
Darius Calliet

Reputation: 81

DELETE query in Postgres hangs indefinitely

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);

Upvotes: 8

Views: 8102

Answers (3)

Berkay Berabi
Berkay Berabi

Reputation: 2348

Not a very useful answer, but it might help someone. I experienced the same issue after doing excessive deletions on the table. I was experimenting with different delete queries and trying to find out which one is the fastest. I was also cancelling queries before they finished. I could not find the underlying reason, but what fixed the problem for me is

My DB was hosted on google cloud with backup support. So, I restored a backup from a few days ago, and the problem was gone.

Upvotes: 2

eastonsuo
eastonsuo

Reputation: 1079

Create index is actually useful for slow delete query. When you run DELETE query with "explain analyze delete from xx" ,and cancel it for too slow ,it will shows:

ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."AAAA" WHERE $1 OPERATOR(pg_catalog.=) "BBBB""

run CREATE INDEX CONCURRENTLY NAME_OF_INDEX ON AAAA(BBBB)

will fix this problem

Upvotes: 3

Churam
Churam

Reputation: 1

I'm not sure (and not able to comment) but I think you are experiencing an heavy re-indexation or a vacuum after your deletion.

Upvotes: 0

Related Questions