Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 6950C1337B47 for ; Tue, 4 Jan 2011 18:29:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 23877-08 for ; Tue, 4 Jan 2011 22:29:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from out-0.mx.aerioconnect.net (out-0-29.mx.aerioconnect.net [216.240.47.89]) by mail.postgresql.org (Postfix) with ESMTP id 980E71337B39 for ; Tue, 4 Jan 2011 18:29:48 -0400 (AST) Received: from idiom.com (postfix@mx0.idiom.com [216.240.32.160]) by out-0.mx.aerioconnect.net (8.13.8/8.13.8) with ESMTP id p04MTkE0003448 for ; Tue, 4 Jan 2011 14:29:46 -0800 X-Client-Authorized: MaGic Cook1e Received: from [192.168.1.128] (75-149-45-155-SFBA.hfc.comcastbusiness.net [75.149.45.155]) by idiom.com (Postfix) with ESMTP id 2E6C52D6014 for ; Tue, 4 Jan 2011 14:29:45 -0800 (PST) Message-ID: <4D239F51.2000102@obviously.com> Date: Tue, 04 Jan 2011 14:29:37 -0800 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20101206 SeaMonkey/2.0.11 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: " in transaction" can't be killed with pg_cancel_backend(). Is it a bug? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.67 on 216.240.47.51 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201101/1 X-Sequence-Number: 34948 Dear helpful postgres people; I've got a database deadlock. The initiating process was " 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 | 2219 | 04:01:59.139841 | production | 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 | 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_objects relation | 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_objects relation | RowShareLock | t | public | user_objects -----------------------------------------------------------------------------------------------------------------