Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Z1ujb-0001k3-79 for pgsql-translators@arkaria.postgresql.org; Mon, 08 Jun 2015 10:56:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Z1uja-0004ba-0Y for pgsql-translators@arkaria.postgresql.org; Mon, 08 Jun 2015 10:56:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Z1ujZ-0004bU-Fu for pgsql-translators@postgresql.org; Mon, 08 Jun 2015 10:56:49 +0000 Received: from smtpbgbr1.qq.com ([54.207.19.206]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1Z1ujS-0003vn-1x for pgsql-translators@postgresql.org; Mon, 08 Jun 2015 10:56:47 +0000 X-QQ-mid: bizesmtp9t1433760981t274t303 Received: from admin130-PC (unknown [122.224.137.162]) by esmtp4.qq.com (ESMTP) with SMTP id 0 for ; Mon, 08 Jun 2015 18:56:20 +0800 (CST) X-QQ-SSF: 00400000000000F0F912B00A0000000 X-QQ-FEAT: X35mVsseVUc9EiHYwerHNY1PDqFqNyQ0jOb9ijBgugY2ZCMxS+uNU2aUygiM9 06ODJkkkAiIDF6c2AgSZwvyNhxwPNY4PgqssO/sJ0fzb7LcxTb7wj8n0oiJYDWrGDmWCjzK lOdlXcLeYImw8vtuNy0m5uBBVAY3TfGKRKO0BN+TiZKAxNlTsO9tz0rK7c7UKh4GXTbrWUB 6IcVYpQ7HLbUYdPNN2g8KW6NazhoueX4= X-QQ-GoodBg: 2 Date: Mon, 8 Jun 2015 18:51:32 +0800 From: "zhiwei.li@melot.cn" To: pgsql-translators Subject: long lock in my pg X-Priority: 3 X-GUID: D717B030-840C-4689-AF18-CA75D2CCE8A9 X-Has-Attach: no X-Mailer: Foxmail 7, 2, 5, 140[cn] Mime-Version: 1.0 Message-ID: <2015060818512695740130@melot.cn> Content-Type: multipart/alternative; boundary="----=_001_NextPart522111852437_=----" X-QQ-SENDSIZE: 520 X-QQ-Bgrelay: 1 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-translators Precedence: bulk Sender: pgsql-translators-owner@postgresql.org This is a multi-part message in MIME format. ------=_001_NextPart522111852437_=---- Content-Type: text/plain; charset="GB2312" Content-Transfer-Encoding: base64 aGkgLiANCiAgbXkgcG9zdGdyZXMgdmVyc2lvbiBpcyA5LjMuNSCho2FuZCBpIHVzZSBwZ2JvdW5j ZXIgd2l0aCBjbGllbnQgdG8gcGcgoaMgIHllc3RlcmRheSAgaSBoYXZlIGEgIHBscGdzcWwgcHJv Y2VkdXJlICB3aXRoo7oNCkNSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9OIGtrcGxheS5wX2tiX3Jv b21faW5mb19ob3RfdXBkYXRlX29ubGluZWNvdW50IA0KKCANCklOIGlfdXNlcmlkIGludGVnZXIs IA0KSU4gaV9vbmxpbmVjb3VudCBpbnRlZ2VyLCANCk9VVCBvX3RhZ2NvZGUgdGV4dCANCikgDQpS RVRVUk5TIHRleHQgQVMgDQokJCANCmJlZ2luIA0KdXBkYXRlIGtrcGxheS5rYl9yb29tX2luZm9f aG90IHNldCBvbmxpbmVfY291bnQ9aV9vbmxpbmVjb3VudCB3aGVyZSB1c2VyX2lkPWlfdXNlcmlk OyANCg0Kb190YWdjb2RlIDo9ICcwMDAwMDAwMCc7IA0KDQpleGNlcHRpb24gd2hlbiBvdGhlcnMg dGhlbiANCg0Kb190YWdjb2RlIDo9ICcwMSc7IA0KDQplbmQgDQokJCANCkxBTkdVQUdFICdwbHBn c3FsJzsNCg0KdGhpcyB1cGRhdGUgaXMgIGZyZXF1ZW50bHkgIC4gaSBhY2NlcHQgaXQgbG9jayBp biBzaG9ydCB0aW1lIKGjIGJ1dCBpIHVzZSAgIHRoZSBmb2xsb3dpbmcgdmlldyAgdG8gbW9uaXRv ciBkYiBsb2NrIA0KDQpjcmVhdGUgdmlldyBsb2NrX21vbml0b3IgYXMgDQpTRUxFQ1QgYmwucGlk IEFTIGJsb2NrZWRfcGlkLCANCmEudXNlbmFtZSBBUyBibG9ja2VkX3VzZXIsIA0KYS5jbGllbnRf YWRkciBBUyBibG9ja2VkX2lwLCANCmthLnF1ZXJ5IEFTIGJsb2NraW5nX3N0YXRlbWVudCwgDQpu b3coKSAtIGthLnF1ZXJ5X3N0YXJ0IEFTIGJsb2NraW5nX2R1cmF0aW9uLCANCmtsLnBpZCBBUyBi bG9ja2luZ19waWQsIA0Ka2EudXNlbmFtZSBBUyBibG9ja2luZ191c2VyLCANCmthLmNsaWVudF9h ZGRyIEFTIGJsb2NraW5nX2lwLCANCmEucXVlcnkgQVMgYmxvY2tlZF9zdGF0ZW1lbnQsIA0Kbm93 KCkgLSBhLnF1ZXJ5X3N0YXJ0IEFTIGJsb2NrZWRfZHVyYXRpb24gDQpGUk9NIHBnX2NhdGFsb2cu cGdfbG9ja3MgYmwgDQpKT0lOIHBnX2NhdGFsb2cucGdfc3RhdF9hY3Rpdml0eSBhIE9OIGEucGlk ID0gYmwucGlkIA0KSk9JTiBwZ19jYXRhbG9nLnBnX2xvY2tzIGtsIE9OIGtsLnRyYW5zYWN0aW9u aWQgPSBibC50cmFuc2FjdGlvbmlkIEFORCBrbC5waWQgIT0gYmwucGlkIA0KSk9JTiBwZ19jYXRh bG9nLnBnX3N0YXRfYWN0aXZpdHkga2EgT04ga2EucGlkID0ga2wucGlkIA0KV0hFUkUgTk9UIGJs LmdyYW50ZWQ7DQoNCmkgZ2V0IHRoZSByZXNhdWx0Og0KYmxvY2tlZF9waWR8YmxvY2tlZF91c2Vy fGJsb2NrZWRfaXB8YmxvY2tpbmdfc3RhdGVtZW50fGJsb2NraW5nX2R1cmF0aW9ufGJsb2NraW5n X3BpZHxibG9ja2luZ191c2VyfGJsb2NraW5nX2lwfGJsb2NrZWRfc3RhdGVtZW50fGJsb2NrZWRf ZHVyYXRpb24gIA0KNzQ3NHxra3BsYXl8MTI3LjAuMC4xfHNlbGVjdCAqIGZyb20ga2twbGF5LnBf a2Jfcm9vbV9pbmZvX2hvdF91cGRhdGVfb25saW5lY291bnQoJDEsJDIpfDA0OjM4OjIwLjcwMjY3 NHw3NDczfGtrcGxheXwxMjcuMC4wLjF8c2VsZWN0ICogZnJvbSBra3BsYXkucF9rYl9yb29tX2lu Zm9faG90X3VwZGF0ZV9vbmxpbmVjb3VudCgkMSwkMil8MDQ6Mzg6MjAuNjg5MjExDQoNCmxvY2sg NCBob3Vycy4gdGhlbiBjcHUgdXNlIG5lYXIgMTAwJSCho3RoZW4gaSBraWxsZWQgIHBpZCA3NDcz IGFuZCAgNzQ3NCAgdGhlbiBzeXN0ZW0gcmV0dXJuIG5vcm1hbCB3b3JraW5noaMNCmkgZG9udCBr bm93IHRoaXMgZWFzeSBzcWwgd2h5IGNhdXNlIGxvY2sgbG9uZ3RpbWWhoyBpcyBteSBhcHBsaWNh dGlvbiBoYXZlIHNvbWUgcHJvYmxlbSBvciB0aGlzIGlzIGJ1ZyB0byBwZ6GjDQp0aGFuayB5b3Ug Zm9yIHlvdSAgaGVscC4NCg0KDQoNCiANCg0KDQoNCnpoaXdlaUxpDQo= ------=_001_NextPart522111852437_=---- Content-Type: text/html; charset="GB2312" Content-Transfer-Encoding: quoted-printable =0A
hi . 
  my= postgres version is 9.3.5 =A1=A3and i use pgbouncer with client to pg =A1= =A3  yesterday  i have a  plpgsql procedure  with=A3= =BA
CREATE O= R REPLACE FUNCTION kkplay.p_kb_room_info_hot_update_onlinecount=0A
(=0A=
IN i_userid integer,=0A
IN i_onlinecount integer,=0A=
OUT o_tagcode text =0A
)=0A
RETURNS text AS=0A
$$= =0A
begin=0A
update kkplay.kb_room_info_hot set online_count=3Di_o= nlinecount where user_id=3Di_userid;=0A
=0A
o_tagcode :=3D '0000= 0000';=0A
=0A
exception when others then =0A
=0A
o_tag= code :=3D '01';=0A
=0A
end=0A
$$=0A
LANGUAGE 'plpgsql';

this update is  frequently  . i accept it lock in short time =A1=A3 but i use=   the following view  to monitor db lock 

create view lock_monitor as =0A
= SELECT bl.pid AS blocked_pid,=0A
a.usename = AS blocked_user,=0A
a.client_addr AS blocke= d_ip,=0A
ka.query AS blocking_statement,=0A
= now() - ka.query_start AS blocking_duration,=0A
kl.pid= AS blocking_pid,=0A
ka.usename AS= blocking_user,=0A
ka.client_addr AS blocking_ip,=0A a.query AS blocked_statement,=0A
now= () - a.query_start AS blocked_duration=0A
FROM pg_catalog.pg_locks = bl=0A
JOIN pg_catalog.pg_stat_activity a ON a.pid =3D bl.pi= d=0A
JOIN pg_catalog.pg_locks kl ON kl.transactionid =3D bl.= transactionid AND kl.pid !=3D bl.pid=0A
JOIN pg_catalog.pg_stat_acti= vity ka ON ka.pid =3D kl.pid=0A
WHERE NOT bl.granted;

i get the resault:
blocked_pid|blocked_user|blocked_ip|blocking_statement|b= locking_duration|blocking_pid|blocking_user|blocking_ip|blocked_statement|= blocked_duration  
7474|kkplay|127.0.= 0.1|select * from kkplay.p_kb_room_info_hot_update_onlinecount($1,$2)|04:3= 8:20.702674|7473|kkplay|127.0.0.1|select * from kkplay.p_kb_room_info_hot_= update_onlinecount($1,$2)|04:38:20.689211

<= /span>
lock 4 hours. then cpu use near 100% =A1=A3the= n i killed  pid 7473 and  7474  then system return normal working=A1=A3=
i= dont know this easy sql why cause lock longtime=A1=A3 is my application h= ave some problem or this is bug to pg=A1=A3
thank you for you  help.<= /span>



 
=0A

=0A
=0A
=0A
=0A
=0A
zhiweiLi
= =0A ------=_001_NextPart522111852437_=------