CODEWITHSUNDEEP
intellij-ideaamazon-redshift
RyanHennig
RyanHennig

Reputation: 1072

Redshift query hangs forever and PG_TERMINATE_BACKEND doesn't work

I was using IntelliJ Ultimate to query Redshift, as well as to debug some python code which was doing a simple one-row insert into Redshift. Normally, the insert statement finishes in less than a second, but it started to hang. It seemed that the query was waiting on a lock held by another query.

I used these queries (among others) to see what was going on:

select t."table", l.*, q.*
from stv_locks l
join svv_table_info t on (l.table_id = t.table_id)
join stl_query q on (l.lock_owner = q.xid)

select * from stv_inflight;

select * from stv_sessions;

And I discovered that a previous run of my python code in the debugger was still holding a write lock, even after the process had exited. I determined that the problematic pid was 30461, so I tried to kill the session with:

select pg_terminate_backend(30461);

However, that didn't seem to have any effect whatsoever. Referring to Amazon's documentation for pg_terminate_backend, I also tried using the CANCEL command on the problematic statement:

cancel 1311;

IntelliJ didn't seem to be sending this statement to the server when I attempted to execute, so I had use the psql command line tool. After executing, the problem still existed and despite much googling I was unable to find an answer. It appeared that I might have to restart the Redshift instance to release the lock.

Upvotes: 3

Views: 5759

Answers (1)

RyanHennig
RyanHennig

Reputation: 1072

The solution, it turns out, was to restart my IDE. Even after I tried disconnecting from Redshift in the IntelliJ database console, it seems to have persisted the connection that was causing the issue. Immediately after I exited the IDE, The lock was released as I verified with the query:

select * from stv_locks

Upvotes: 2

Related Questions