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

0

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 <pid>
CANCEL

and

heavy_livestock=> select pg_terminate_backend(<pid>);
 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 = <pid>;

         txn_start          |       lock_mode       | lockable_object_type | relation | granted
----------------------------+-----------------------+----------------------+----------+---------
 2019-03-02 02:14:18.462468 | AccessExclusiveLock   | relation             |        1 | f
 2019-03-02 02:14:18.462468 | RowExclusiveLock      | relation             |        2 | t
 2019-03-02 02:14:18.462468 | AccessShareLock       | relation             |        3 | t
 2019-03-02 02:14:18.462468 | AccessShareLock       | relation             |        4 | t
 2019-03-02 02:14:18.462468 | AccessShareLock       | relation             |        1 | t
 2019-03-02 02:14:18.462468 | AccessExclusiveLock   | relation             |        5 | t
 2019-03-02 02:14:18.462468 | ShareRowExclusiveLock | relation             |        5 | t
 2019-03-02 02:14:18.462468 | AccessShareLock       | relation             |        5 | t
 2019-03-02 02:14:18.462468 | ExclusiveLock         | transactionid        |          | t
(9 rows)

What else can I try?

Edited by: HeavyLivestock on Mar 5, 2019 9:11 PM

asked 5 years ago726 views
4 Answers
0

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

Toebs2
answered 5 years ago
0

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.

answered 5 years ago
0

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

Remember that SVV_TRANSACTIONS query?

         txn_start          |       lock_mode       | lockable_object_type | relation | granted
----------------------------+-----------------------+----------------------+----------+---------
 2019-03-02 02:14:18.462468 | AccessExclusiveLock   | relation             |        1 | f

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.

answered 5 years ago
0

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.

Toebs2
answered 5 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions