系统表:pg_stat_activity
创建函数:rds_pg_stat_activity
--RDS函数脚本
CREATE OR REPLACE FUNCTION public.rds_pg_stat_activity()
RETURNS SETOF pg_stat_activity AS
$BODY$
DECLARE
user_cando bool;
ref refcursor;
myrec pg_catalog.pg_stat_activity%rowtype;
BEGIN
user_cando = manage_ext_check_permissions();
if user_cando = false then
RAISE NOTICE 'permissions denied';
RAISE NOTICE 'users need to be superuser or have createdb createrole login simultaneously';
return;
end if;
OPEN ref FOR SELECT * FROM pg_catalog.pg_stat_activity;
LOOP
FETCH ref into myrec;
EXIT WHEN NOT FOUND;
return next myrec;
end LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100
ROWS 1000;
ALTER FUNCTION public.rds_pg_stat_activity()
OWNER TO hrp_1001;
/
查询当前正在运行的sql
select * from rds_pg_stat_activity() where datname='cloudhrp' and usename = 'hrp_210001' and state !='idle';
select * from pg_stat_activity --也可直接进行查询
datname:数据库名称;
usename:数据库用户名
state:状态,常见的几种如下
> active:表示当前用户正在执行查询等操作。
> idle:表示当前用户空闲。
> idle in transaction:表示当前用户在事务中。
> idle in transaction (aborted): 表示当前用户在事务中,但是已经发生错误。
>pid:后端进程id
query:sql
进程杀死:
select PG_TERMINATE_BACKEND(pid);
pg_cancel_backend() 取消后台操作,回滚未提交事物
PG_TERMINATE_BACKEND() 中断session,回滚未提交事物
这里和oracle类似kill session的操作是PG_TERMINATE_BACKEND()