hi .
my postgres version is 9.3.5 。and i use pgbouncer with client to pg 。 yesterday i have a plpgsql procedure with：
CREATE OR REPLACE FUNCTION kkplay.p_kb_room_info_hot_update_onlinecount
(
IN i_userid integer,
IN i_onlinecount integer,
OUT o_tagcode text
)
RETURNS text AS
$$
begin
update kkplay.kb_room_info_hot set online_count=i_onlinecount where user_id=i_userid;
o_tagcode := '00000000';
exception when others then
o_tagcode := '01';
end
$$
LANGUAGE 'plpgsql';
this update is frequently . i accept it lock in short time 。 but i use the following view to monitor db lock
create view lock_monitor as
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
a.client_addr AS blocked_ip,
ka.query AS blocking_statement,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
ka.client_addr AS blocking_ip,
a.query AS blocked_statement,
now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;
i get the resault:
blocked_pid|blocked_user|blocked_ip|blocking_statement|blocking_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:38:20.702674|7473|kkplay|127.0.0.1|select * from kkplay.p_kb_room_info_hot_update_onlinecount($1,$2)|04:38:20.689211
lock 4 hours. then cpu use near 100% 。then i killed pid 7473 and 7474 then system return normal working。
i dont know this easy sql why cause lock longtime。 is my application have some problem or this is bug to pg。
thank you for you help.
