neither CANCEL nor PG_TERMINATE_BACKEND() kill query

Question

I have a transaction (that performs a deep copy, for cluster maintenance) that is abandoned and _just won't die_. The following query was originally ran by a maintenance job:

```
SET wlm_query_slot_count to 3;
SET search_path TO "schema";
BEGIN;
CREATE TABLE foo (LIKE bar);
INSERT INTO foo (SELECT * FROM bar);
DROP TABLE bar;
ALTER TABLE foo RENAME TO bar;
COMMIT;
ANALYZE bar;
RESET wlm_query_slot_count;
 ```

I've tried

```
heavy_livestock=> CANCEL 
CANCEL
```

and

```
heavy_livestock=> select pg_terminate_backend();
 pg_terminate_backend
----------------------
                    1
(1 row)
```

but the query won't die: it still shows up in

```
SELECT * FROM STV_RECENTS WHERE QUERY ILIKE '%bar%';
```

and

```
heavy_livestock=> select txn_start, lock_mode, lockable_object_type, relation, granted from svv_transactions where  pid = ;

What else can I try?  
  
Edited by: HeavyLivestock on Mar 5, 2019 9:11 PM

Max Ganz II · Answer

Very interesting - and thankyou for reporting back on what you found in the end.  
  
I think I've read other people posting here with the same problem - query will not die - and I think also this has always been due to interactions with other queries, so that the query **can't** die.  
  
When I come across this again, I'll know what to say.

HeavyLivestock · Answer

The query has finally died, of its own accord. Still interested to know how to kill such a query without waiting 5 days for a natural death.

Max Ganz II · Answer

> CREATE TABLE foo (LIKE bar);  
  
As an aside, you know when you use CTL, the new table loses the PK and FK constraints of the original?  (and also any default values, in this case, since the option to carry them over is not set).

HeavyLivestock · Answer

This situation came up again, and this time we managed to actively kill the query.  
  
Remember that SVV_TRANSACTIONS query?

The fact that this lock was _not_ granted was the key.  
  
> If the result in the granted column is f (false), the process can't get the lock it needs because another transaction in another session is holding the lock. The blocking_pid column shows the process ID of the session that is holding the lock. In the previous example, PID 19813 is holding the lock.  
  
Use the query from  
https://aws.amazon.com/premiumsupport/knowledge-center/prevent-locks-blocking-queries-redshift/  
to get the blocking_pid column.  
  
Cancelling the blocking PID allowed us to kill the deep copy.

neither CANCEL <pid> nor PG_TERMINATE_BACKEND(<pid>) kill query

Relevant content