本篇内容介绍了“PostgreSQL中函数pg_blocking_pids的作用是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
目前创新互联已为近千家的企业提供了网站建设、域名、虚拟主机、成都网站托管、企业网站设计、大埔网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
函数pg_blocking_pids用于获取哪些进程(输出参数)阻塞了某个进程(输入参数).
我们在执行某些操作时,console可能会挂起没有输出,这时候你没有办法判断是因为执行很慢还是因为被阻塞了,通过pg_blocking_pids可以判断是否存在阻塞.
----------- session 1
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 2.877 ms
[local]:5432 pg12@testdb=#* delete from tbl where id = 1;
DELETE 1
Time: 1.113 ms
----------- session 2
[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 1.700 ms
[local]:5432 pg12@testdb=#* delete from tbl where id = 1;
----------- session 3
[local]:5432 pg12@testdb=# select * from pg_blocking_pids(1628);
pg_blocking_pids
------------------
{1541}
(1 row)
Time: 1.838 ms
[local]:5432 pg12@testdb=#通过查询可发现阻塞pid = 1628的进程1541.这时候可以查询pg_locks和进程活动信息获取更详细的信息
[local]:5432 pg12@testdb=# select pid,locktype,relation::regclass,mode,page,tuple,virtualxid,transactionid,virtualtransaction,granted,fastpath from pg_locks where relation='tbl'::regclass; -[ RECORD 1 ]------+-------------------- pid | 1628 locktype | relation relation | tbl mode | RowExclusiveLock page | tuple | virtualxid | transactionid | virtualtransaction | 5/27 granted | t fastpath | t -[ RECORD 2 ]------+-------------------- pid | 1541 locktype | relation relation | tbl mode | RowExclusiveLock page | tuple | virtualxid | transactionid | virtualtransaction | 3/123 granted | t fastpath | t -[ RECORD 3 ]------+-------------------- pid | 1628 locktype | tuple relation | tbl mode | AccessExclusiveLock page | 0 tuple | 1 virtualxid | transactionid | virtualtransaction | 5/27 granted | t fastpath | f Time: 4.863 ms
进程活动信息
[local]:5432 pg12@testdb=# select * from pg_stat_activity where pid = 1541; -[ RECORD 1 ]----+------------------------------ datid | 16384 datname | testdb pid | 1541 usesysid | 10 usename | pg12 application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2019-08-14 10:46:54.422873+08 xact_start | 2019-08-14 12:06:14.357368+08 query_start | 2019-08-14 12:06:16.982161+08 state_change | 2019-08-14 12:06:16.983058+08 wait_event_type | Client wait_event | ClientRead state | idle in transaction backend_xid | 625 backend_xmin | query | delete from tbl where id = 1; backend_type | client backend Time: 8.979 ms
“PostgreSQL中函数pg_blocking_pids的作用是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！
