PostgreSQL如何监控系统中是否有长事务？

例子：
长事务 30 min

select datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from pg_stat_activity where state<>
$$
idle
$$
 and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval 
$$
30 min
$$
 order by xact_start;

2PC长事务 30 min

select name,statement,prepare_time,now()-prepare_time,parameter_types,from_sql from pg_prepared_statements where now()-prepare_time > interval 
$$
30 min
$$
 order by prepare_time;

可以写成脚本
https://github.com/digoal/pgsql_admin_script/blob/master/generate_report.sh

echo "----->>>---->>>  长事务, 2PC: "
psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE --pset=pager=off -q -x -c 'select datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from pg_stat_activity where state<>
$$
idle
$$
 and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval 
$$
30 min
$$
 order by xact_start'
psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE --pset=pager=off -q -x -c 'select name,statement,prepare_time,now()-prepare_time,parameter_types,from_sql from pg_prepared_statements where now()-prepare_time > interval 
$$
30 min
$$
 order by prepare_time'
echo "建议: "
echo "    长事务过程中产生的垃圾, 无法回收, 建议不要在数据库中运行LONG SQL, 或者错开DML高峰时间去运行LONG SQL. 2PC事务一定要记得尽快结束掉, 否则可能会导致数据库膨胀. "
echo "    参考: http://blog.163.com/digoal@126/blog/static/1638770402015329115636287/ "
echo -e "\n"