Обсуждение: " in transaction" can't be killed with pg_cancel_backend(). Is it a bug?

Поиск
Список
Период
Сортировка

" in transaction" can't be killed with pg_cancel_backend(). Is it a bug?

От
Bryce Nesbitt
Дата:
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?

От
Bryce Nesbitt
Дата:
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?

От
Michael Glaesemann
Дата:
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

Re: " in transaction" can't be killed with pg_cancel_backend(). Is it a bug?

От
bricklen
Дата:
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+