这篇文章主要介绍“PostgreSQL中怎么在pg_locks和pg_stat_activity两张基表基础上创建的视图”，在日常操作中，相信很多人在PostgreSQL中怎么在pg_locks和pg_stat_activity两张基表基础上创建的视图问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中怎么在pg_locks和pg_stat_activity两张基表基础上创建的视图”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

显示阻塞信息的视图:

CREATEORREPLACEVIEWvw_lockinfoASSELECTblocked_locks.pidASblocked_pid,blocked_activity.usenameASblocked_user,blocking_locks.pidASblocking_pid,blocking_activity.usenameASblocking_user,blocked_activity.queryASblocked_statement,blocking_activity.queryAScurrent_statement_in_blocking_processFROMpg_catalog.pg_locksblocked_locksJOINpg_catalog.pg_stat_activityblocked_activityONblocked_activity.pid=blocked_locks.pidJOINpg_catalog.pg_locksblocking_locksONblocking_locks.locktype=blocked_locks.locktypeANDblocking_locks.DATABASEISNOTDISTINCTFROMblocked_locks.DATABASEANDblocking_locks.relationISNOTDISTINCTFROMblocked_locks.relationANDblocking_locks.pageISNOTDISTINCTFROMblocked_locks.pageANDblocking_locks.tupleISNOTDISTINCTFROMblocked_locks.tupleANDblocking_locks.virtualxidISNOTDISTINCTFROMblocked_locks.virtualxidANDblocking_locks.transactionidISNOTDISTINCTFROMblocked_locks.transactionidANDblocking_locks.classidISNOTDISTINCTFROMblocked_locks.classidANDblocking_locks.objidISNOTDISTINCTFROMblocked_locks.objidANDblocking_locks.objsubidISNOTDISTINCTFROMblocked_locks.objsubidANDblocking_locks.pid!=blocked_locks.pidJOINpg_catalog.pg_stat_activityblocking_activityONblocking_activity.pid=blocking_locks.pidWHERENOTblocked_locks.GRANTED;[local]:5432pg12@testdb=#CREATEORREPLACEVIEWvw_lockinfopg12@testdb-#ASpg12@testdb-#SELECTblocked_locks.pidASblocked_pid,pg12@testdb-#blocked_activity.usenameASblocked_user,pg12@testdb-#blocking_locks.pidASblocking_pid,pg12@testdb-#blocking_activity.usenameASblocking_user,pg12@testdb-#blocked_activity.queryASblocked_statement,pg12@testdb-#blocking_activity.queryAScurrent_statement_in_blocking_processpg12@testdb-#FROMpg_catalog.pg_locksblocked_lockspg12@testdb-#JOINpg_catalog.pg_stat_activityblocked_activityONblocked_activity.pid=blocked_locks.pidpg12@testdb-#JOINpg_catalog.pg_locksblocking_lockspg12@testdb-#ONblocking_locks.locktype=blocked_locks.locktypepg12@testdb-#ANDblocking_locks.DATABASEISNOTDISTINCTFROMblocked_locks.DATABASEpg12@testdb-#ANDblocking_locks.relationISNOTDISTINCTFROMblocked_locks.relationpg12@testdb-#ANDblocking_locks.pageISNOTDISTINCTFROMblocked_locks.pagepg12@testdb-#ANDblocking_locks.tupleISNOTDISTINCTFROMblocked_locks.tuplepg12@testdb-#ANDblocking_locks.virtualxidISNOTDISTINCTFROMblocked_locks.virtualxidpg12@testdb-#ANDblocking_locks.transactionidISNOTDISTINCTFROMblocked_locks.transactionidpg12@testdb-#ANDblocking_locks.classidISNOTDISTINCTFROMblocked_locks.classidpg12@testdb-#ANDblocking_locks.objidISNOTDISTINCTFROMblocked_locks.objidpg12@testdb-#ANDblocking_locks.objsubidISNOTDISTINCTFROMblocked_locks.objsubidpg12@testdb-#ANDblocking_locks.pid!=blocked_locks.pidpg12@testdb-#pg12@testdb-#JOINpg_catalog.pg_stat_activityblocking_activityONblocking_activity.pid=blocking_locks.pidpg12@testdb-#WHERENOTblocked_locks.GRANTED;CREATEVIEWTime:131.424ms[local]:5432pg12@testdb=#select*fromvw_lockinfo;-[RECORD1]-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------blocked_pid|2184blocked_user|pg12blocking_pid|2863blocking_user|pg12blocked_statement|updatet_locksetid=1000whereid=1;current_statement_in_blocking_process|selectpid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpathfrompg_lockswhererelation='t_lock'::regclass;Time:21.032ms

显示带有时间属性的locks

CREATEORREPLACEVIEWvw_lockinfo_timeASSELECTa.datname,l.relation::regclass,l.transactionid,l.mode,l.GRANTED,a.usename,a.query,a.query_start,age(now(),a.query_start)AS"age",a.pidFROMpg_stat_activityaJOINpg_lockslONl.pid=a.pidORDERBYa.query_start;[local]:5432pg12@testdb=#CREATEORREPLACEVIEWvw_lockinfo_timepg12@testdb-#ASpg12@testdb-#SELECTa.datname,pg12@testdb-#l.relation::regclass,pg12@testdb-#l.transactionid,pg12@testdb-#l.mode,pg12@testdb-#l.GRANTED,pg12@testdb-#a.usename,pg12@testdb-#a.query,pg12@testdb-#a.query_start,pg12@testdb-#age(now(),a.query_start)AS"age",pg12@testdb-#a.pidpg12@testdb-#FROMpg_stat_activityapg12@testdb-#JOINpg_lockslONl.pid=a.pidpg12@testdb-#ORDERBYa.query_start;CREATEVIEWTime:17.799ms[local]:5432pg12@testdb=#select*fromvw_lockinfo_time;-[RECORD1]-+---------------------------------------------------------------------------------------------------------------------------------------------------------------------datname|testdbrelation|t_locktransactionid|mode|RowExclusiveLockgranted|tusename|pg12query|selectpid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpathfrompg_lockswhererelation='t_lock'::regclass;query_start|2019-08-1315:32:23.139886+08age|00:11:29.095421pid|2863...

到此，关于“PostgreSQL中怎么在pg_locks和pg_stat_activity两张基表基础上创建的视图”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！