PostgreSQL中函数pg_blocking_pids的作用是什么

本篇内容介绍了"PostgreSQL中函数pg_blocking_pids的作用是什么"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

函数pg_blocking_pids用于获取哪些进程(输出参数)阻塞了某个进程(输入参数).

我们在执行某些操作时,console可能会挂起没有输出,这时候你没有办法判断是因为执行很慢还是因为被阻塞了,通过pg_blocking_pids可以判断是否存在阻塞.

----------- session 1[local]:5432 pg12@testdb=# begin;BEGINTime: 2.877 ms[local]:5432 pg12@testdb=#* delete from tbl where id = 1;DELETE 1Time: 1.113 ms----------- session 2[local]:5432 pg12@testdb=# begin;BEGINTime: 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                | 1628locktype           | relationrelation           | tblmode               | RowExclusiveLockpage               | tuple              | virtualxid         | transactionid      | virtualtransaction | 5/27granted            | tfastpath           | t-[ RECORD 2 ]------+--------------------pid                | 1541locktype           | relationrelation           | tblmode               | RowExclusiveLockpage               | tuple              | virtualxid         | transactionid      | virtualtransaction | 3/123granted            | tfastpath           | t-[ RECORD 3 ]------+--------------------pid                | 1628locktype           | tuplerelation           | tblmode               | AccessExclusiveLockpage               | 0tuple              | 1virtualxid         | transactionid      | virtualtransaction | 5/27granted            | tfastpath           | fTime: 4.863 ms

进程活动信息

[local]:5432 pg12@testdb=# select * from pg_stat_activity where pid = 1541;-[ RECORD 1 ]----+------------------------------datid            | 16384datname          | testdbpid              | 1541usesysid         | 10usename          | pg12application_name | psqlclient_addr      | client_hostname  | client_port      | -1backend_start    | 2019-08-14 10:46:54.422873+08xact_start       | 2019-08-14 12:06:14.357368+08query_start      | 2019-08-14 12:06:16.982161+08state_change     | 2019-08-14 12:06:16.983058+08wait_event_type  | Clientwait_event       | ClientReadstate            | idle in transactionbackend_xid      | 625backend_xmin     | query            | delete from tbl where id = 1;backend_type     | client backendTime: 8.979 ms

"PostgreSQL中函数pg_blocking_pids的作用是什么"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！