title: Redshift query hangs forever and PG_TERMINATE_BACKEND doesn't work
body: <p>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.</p>
  
  <p>I used these queries (among others) to see what was going on:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>select pg_terminate_backend(30461);
  </code></pre>
  
  <p>However, that didn't seem to have any effect whatsoever. Referring to <a href="http://docs.aws.amazon.com/redshift/latest/dg/PG_TERMINATE_BACKEND.html" rel="nofollow">Amazon's documentation for pg_terminate_backend</a>, I also tried using the CANCEL command on the problematic statement:</p>
  
  <pre><code>cancel 1311;
  </code></pre>
  
  <p>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.</p>