Thread: " in transaction" can't be killed with pg_cancel_backend().
Is it a bug?
Dear helpful postgres people;
I've got a database deadlock. The initiating process was "<IDLE> in
transaction" with PID 2219.
Use of pg_cancel_backend() returns true, but does not actually kill the
process.
When this happens I kill the PID at the Unix level, which feels sort of
stupid, like driving on the hood
of a car on the freeway without a safety harness.
Should I expect pg_cancel_backend() to work in a case like this? If not
does this sound like a reportable bug?
Also: is there a way I can get the value of $1 in the select for update
shown below?
-----------------------------------------------------------------------------------------------------------------
# select version();
PostgreSQL 8.3.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
# select procpid,now()-query_start,usename,substr(current_query,0,1000)
from pg_stat_activity order by 2 desc;
procpid | ?column? | usename |
---------+-------------------------+------------+-------------------------------------------------------------------
32507| 22:31:09.736599 | jira | <IDLE> 2219 | 04:01:59.139841 | production | <IDLE> in
transaction 3428 | 03:58:06.149227 | production | select object,utf8
from user_objects where userid = $1 for update 6574 | 03:46:35.627382 | production | select object,utf8
from user_objects where userid = $1 for update 4456 | 02:41:33.680257 | production | <IDLE> 26217 |
02:26:49.609589 | production | select object,utf8
from user_objects where userid = $1 for update 29562 | 02:17:00.434927 | production | select object,utf8
from user_objects where userid = $1 for update 2123 | 00:28:04.877832 | production | select object,utf8
from user_objects where userid = $1 for update
....
=# SELECT l.locktype,c.relname,l.pid,l.mode,granted from pg_locks
l,pg_class c where l.relation=c.oid order by relname,granted;
locktype | relname | pid |
mode | granted
----------+-------------------------------------+-------+------------------+--------- relation | context_keyvals
| 5841 |
RowExclusiveLock | t relation | context_keyvals_ckey | 5841 |
RowExclusiveLock | t relation | context_objects | 2219 |
RowShareLock | t relation | context_objects_pkey | 2219 |
AccessShareLock | t relation | contexts | 2549 |
AccessShareLock | t relation | contexts | 5841 |
RowShareLock | t relation | contexts_id_key | 5841 |
AccessShareLock | t relation | contexts_pkey | 5841 |
AccessShareLock | t relation | contexts_publication_date_idx | 5841 |
AccessShareLock | t relation | contexts_site_key_ct_id_journal_key | 5841 |
AccessShareLock | t relation | contexts_site_key_ct_type_idx | 5841 |
AccessShareLock | t relation | pg_class | 3738 |
AccessShareLock | t relation | pg_class_oid_index | 3738 |
AccessShareLock | t relation | pg_class_relname_nsp_index | 3738 |
AccessShareLock | t relation | pg_locks | 3738 |
AccessShareLock | t relation | pg_toast_3794455612 | 10155 |
AccessShareLock | t relation | pg_toast_3794455612_index | 10155 |
AccessShareLock | t tuple | user_objects | 26217 |
ExclusiveLock | f tuple | user_objects | 6574 |
ExclusiveLock | f tuple | user_objects | 2123 |
ExclusiveLock | f tuple | user_objects | 29562 |
ExclusiveLock | f relation | user_objects | 2123 |
RowShareLock | t relation | user_objects | 26217 |
RowShareLock | t relation | user_objects | 10155 |
AccessShareLock | t tuple | user_objects | 3428 |
ExclusiveLock | t relation | user_objects | 3428 |
RowShareLock | t relation | user_objects | 6574 |
RowShareLock | t relation | user_objects | 2219 |
RowShareLock | t relation | user_objects | 29562 |
RowShareLock | t relation | user_objects_pkey | 6574 |
AccessShareLock | t relation | user_objects_pkey | 2123 |
AccessShareLock | t relation | user_objects_pkey | 26217 |
AccessShareLock | t relation | user_objects_pkey | 3428 |
AccessShareLock | t relation | user_objects_pkey | 29562 |
AccessShareLock | t relation | user_objects_pkey | 2219 |
AccessShareLock | t relation | user_objects_pkey | 10155 |
AccessShareLock | t
# SELECT locktype, mode, granted, schemaname, relname FROM pg_locks INNER JOIN pg_stat_user_tables ON
pg_locks.relation= pg_stat_user_tables.relid WHERE pg_locks.pid='2219';
locktype | mode | granted | schemaname | relname
----------+--------------+---------+------------+----------------- relation | RowShareLock | t | public |
context_objectsrelation | RowShareLock | t | public | user_objects
# select pg_cancel_backend(2219); pg_cancel_backend
------------------- t
# SELECT locktype, mode, granted, schemaname, relname FROM pg_locks INNER JOIN pg_stat_user_tables ON
pg_locks.relation= pg_stat_user_tables.relid WHERE pg_locks.pid='2219';
locktype | mode | granted | schemaname | relname
----------+--------------+---------+------------+----------------- relation | RowShareLock | t | public |
context_objectsrelation | RowShareLock | t | public | user_objects
-----------------------------------------------------------------------------------------------------------------
Re: " in transaction" can't be killed with pg_cancel_backend().
Is it a bug?
From
Bryce Nesbitt
Date:
Michael Glaesemann wrote: > On Jan 4, 2011, at 17:29, Bryce Nesbitt wrote: > >> Should I expect pg_cancel_backend() to work in a case like this? If not does this sound like a reportable bug? >> > pg_cancel_backend() cancels the running query for that backend. In the case of<IDLE>, there is no running query for thatbackend. > > Likely you're looking for pg_terminate_backend(). > Michael Glaesemann > grzm seespotcode net > Hmm, yes. Though it seems to not be in the documentation, or, for that matter, the current code: utils/adt/misc.c #*ifdef* NOT_USED //* Disabled in 8.0 due to reliability concerns; FIXME someday *// *pg_terminate_backend*(PG_FUNCTION_ARGS # select pg_terminate_backend(5); ERROR: function pg_terminate_backend(integer) does not exist LINE 1: select pg_terminate_backend(5); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Re: " in transaction" can't be killed with pg_cancel_backend(). Is it a bug?
From
Michael Glaesemann
Date:
On Jan 4, 2011, at 17:29, Bryce Nesbitt wrote: > Should I expect pg_cancel_backend() to work in a case like this? If not does this sound like a reportable bug? pg_cancel_backend() cancels the running query for that backend. In the case of <IDLE>, there is no running query for thatbackend. Likely you're looking for pg_terminate_backend(). Michael Glaesemann grzm seespotcode net
On Tue, Jan 4, 2011 at 2:47 PM, Bryce Nesbitt <bryce2@obviously.com> wrote: > Michael Glaesemann wrote: >> Likely you're looking for pg_terminate_backend(). >> Michael Glaesemann >> grzm seespotcode net >> > > Hmm, yes. Though it seems to not be in the documentation, or, for that > matter, the current code: 8.4+