" in transaction" can't be killed with pg_cancel_backend().
Is it a bug?
От | Bryce Nesbitt |
---|---|
Тема | " |
Дата | |
Msg-id | 4D239F51.2000102@obviously.com обсуждение исходный текст |
Ответы |
Re: " |
Список | pgsql-sql |
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 -----------------------------------------------------------------------------------------------------------------