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

This article mainly introduces "how to understand the situation of session hang in PostgreSQL". In daily operation, I believe many people have doubts about how to understand the situation of session hang in PostgreSQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubt of "how to understand the situation of session hang in PostgreSQL"! Next, please follow the editor to study!

When executing SQL, you may encounter the situation of session hang. At this time, we do not know whether the execution of SQL itself is slow or because lock causes hang, so generally speaking, you need to query pg_stat_activity, pg_locks and other system tables to confirm. In addition, PG provides a timeout mechanism to handle this situation through statement timeout.

Session 1

Create data tables, start transactions, execute queries

Testdb=# create table t_timeout (id int); CREATE TABLEtestdb=# testdb=# begin;BEGINtestdb=# testdb=# select count (*) from tweeted timeout; count-0 (1 row) testdb=# select * from pg_locks where pid = pg_backend_pid () Locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath-+- -+-relation | 16384 | 11645 | | | 3x94 | 1719 | AccessShareLock | t | t virtualxid | 3x94 | 3gam94 | 1719 | ExclusiveLock | t | t relation | 16384 | 286770 | | | | 3x94 | 1719 | AccessShareLock | t | f (3 rows) testdb=# |

Session 2

Execute the alter table command, hang live

Testdb=#-- session 2testdb=# alter table t_timeout add column C1 int;-- hangs

Set 50ms timeout. SQL returned a timeout error.

Testdb=# begin;BEGINtestdb=# SET statement_timeout = 50 × SETtestdbsites # alter table t_timeout add column C1 int;ERROR: canceling statement due to statement timeouttestdb=#

However, such a setting requires DBA to have a preliminary estimate of the execution time of SQL. For example, adding column operations should normally be returned in 10ms. There is no problem in setting timeout 50ms, but for operations such as vacuum full, setting it to 50ms is not appropriate.

Testdb=# SET statement_timeout = 50 ~ (th) SETtestDB # vacuum full;ERROR: canceling statement due to statement timeouttestdb=#

In other words, setting timeout will result in accidental injury and should be used with caution.

At this point, the study on "how to understand the session hang situation 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.