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

This article introduces the relevant knowledge of "what is the function of pg_blocking_pids in PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The function pg_blocking_pids is used to get which processes (output parameters) block a process (input parameters).

When we perform some operations, console may hang without output. At this time, you can't tell whether it is because the execution is slow or because it is blocked. You can tell whether there is blocking by pg_blocking_pids.

-session 1 [local]: 5432 pg12@testdb=# begin;BEGINTime: 2.877 ms [local]: 5432 pg12@testdb=#* delete from tbl where id = 1 pg12@testdb=# begin;BEGINTime 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=#

Through the query, we can find that the process blocking pid = 1628 1541. At this time, you can query pg_locks and process activity information for more detailed information.

[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

Process activity information

[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, "what is the function of pg_blocking_pids in PostgreSQL"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.