Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to create views based on pg_locks and pg_stat_activity base tables in PostgreSQL". In daily operation, I believe that many people have doubts about how to create views based on pg_locks and pg_stat_activity base tables in PostgreSQL. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful to answer the question of "how to create views based on pg_locks and pg_stat_activity base tables in PostgreSQL"! Next, please follow the editor to study!

View that displays blocking information:

CREATE OR REPLACE VIEW vw_lockinfoASSELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement Blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks. Relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid! = blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED [local]: 5432 pg12@testdb=# CREATE OR REPLACE VIEW vw_lockinfopg12@testdb-# ASpg12@testdb-# SELECT blocked_locks.pid AS blocked_pid,pg12@testdb-# blocked_activity.usename AS blocked_user,pg12@testdb-# blocking_locks.pid AS blocking_pid,pg12@testdb-# blocking_activity.usename AS blocking_user,pg12@testdb-# blocked_activity.query AS blocked_statement Pg12@testdb-# blocking_activity.query AS current_statement_in_blocking_processpg12@testdb-# FROM pg_catalog.pg_locks blocked_lockspg12@testdb-# JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pidpg12@testdb-# JOIN pg_catalog.pg_locks blocking_locks pg12@testdb-# ON blocking_locks.locktype = blocked_locks.locktypepg12 @ testdb-# AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASEpg12@testdb-# AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relationpg12@testdb-# AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.pagepg12@testdb-# AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuplepg12@testdb-# AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxidpg12@testdb-# AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionidpg12@testdb-# AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classidpg12@testdb-# AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objidpg12@testdb-# AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubidpg12@testdb-# AND blocking_locks.pid! = blocked_locks.pidpg12@testdb-# pg12@testdb-# JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pidpg12@testdb-# WHERE NOT blocked_locks.GRANTED CREATE VIEWTime: 131.424 ms [local]: 5432 pg12@testdb=# select * from vw_lockinfo -[RECORD 1]-+- -blocked_pid | 2184blocked_user | pg12blocking_pid | | 2863blocking_user | pg12blocked_statement | update t_lock set id = 1000 where id = 1 | Current_statement_in_blocking_process | select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='t_lock'::regclass;Time: 21.032 ms

Show locks with time attribute

CREATE OR REPLACE VIEW vw_lockinfo_timeASSELECT a.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.pidFROM pg_stat_activity aJOIN pg_locks l ON l.pid = a.pidORDER BY a.query_start [local]: 5432 pg12@testdb=# CREATE OR REPLACE VIEW vw_lockinfo_timepg12@testdb-# ASpg12@testdb-# SELECT a.datnamegramme pg12antitestdbslay # l.GRANTEDed pg12antitestdbslay # l.usenamedimpg12antitestdbcombe # l.transactioniddimpg12perfect testdbcombe # l.mode.pg12perfect testdbcombe # l.GRANTEDdir pg12antitestdbcombe # a.query Pg12@testdb-# a.querystarting ON l.pid pg12destroy testdbmuri # age (now (), a.query_start) AS "age", pg12@testdb-# a.pidpg12@testdb-# FROM pg_stat_activity apg12@testdb-# JOIN pg_locks l ON l.pid = a.pidpg12@testdb-# ORDER BY a.query_start CREATE VIEWTime: 17.799 ms [local]: 5432 pg12@testdb=# select * from vw_lockinfo_time -[RECORD 1]-+- -datname | testdbrelation | t_locktransactionid | mode | RowExclusiveLockgranted | tusename | pg12query | select pid Locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='t_lock'::regclass Query_start | 2019-08-13 15:32:23.139886+08age | 00:11:29.095421pid | 2863. At this point, the study on "how to create views based on pg_locks and pg_stat_activity in PostgreSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.