如何解決PostgreSQL執(zhí)行語句長時間卡著不動不報錯也不執(zhí)行的問題

更新時間：2024年02月02日 15:30:40 作者：溝溝里的農民

某日開發(fā)同事上報一sql性能問題,一條查詢好似一直跑不出結果,查詢了n小時,還未返回結果,這篇文章主要給大家介紹了關于如何解決PostgreSQL執(zhí)行語句長時間卡著不動不報錯也不執(zhí)行問題的相關資料,需要的朋友可以參考下

1 問題現象

執(zhí)行SQL語句，卡著不動，不成功也不執(zhí)行，就像掛住了一樣。

truncate table simple;

2 原因分析

一般來說，語句呈現卡著的狀態(tài)，主要會是兩種原因比較多，

原因1：SQL語句是一個耗時操作，正常場景下執(zhí)行的時候本來就耗時。

原因2：SQL語句中涉及到的表或者說對象處于鎖定狀態(tài)。

現在來看當前的問題，truncate table simple; 我們看這個語句應該會執(zhí)行的很快才對，

如果是delete * from simple;那如果simple表里面數據量大的話是會比較慢的。

因此，這里大概率是表被鎖住了。

3 數據庫表被鎖住了，如何處理?

3.1 查詢一下當前數據庫的活動監(jiān)控pg_stat_activity

執(zhí)行語句：

select pg_blocking_pids(pid),pid,now()-xact_start,wait_event,wait_event_type,substr(query,1,100) from pg_stat_activity where state <> ‘idle' order by 3 desc;

test=# select pg_blocking_pids(pid),pid,now()-xact_start,wait_event,wait_event_type,substr(query,1,100) from pg_stat_activity where state <> 'idle' order by 3 desc;
 pg_blocking_pids | pid |    ?column?     | wait_event | wait_event_type |                                                substr

------------------+-----+-----------------+------------+-----------------+----------------------------------------------------------------------------------------------
--------
 {}               | 592 | 00:53:35.188996 | ClientRead | Client          | lock table simple in access  exclusive mode;
 {592}            | 641 | 00:17:37.498617 | relation   | Lock            | truncate table simple;
 {}               | 750 | 00:00:00        |            |                 | select pg_blocking_pids(pid),pid,now()-xact_start,wait_event,wait_event_type,substr(query,1,1
00) fro
(3 rows)

通過上面執(zhí)行語句得到的結果，可以看到我們執(zhí)行truncate table simple的語句進程id是641，它處于Lock狀態(tài)，Lock的原因是因為592阻塞導致。

因此，要先解決592進程。

3.2 中斷阻塞進程

pg_terminate_backend(需要被中斷的進程號)

pg_terminate_backend函數說明：

test=# select pg_terminate_backend(592);
 pg_terminate_backend
----------------------
 t
(1 row)

3.3 檢查前面的執(zhí)行是否成功

剛剛卡著的，中斷阻塞的進程后，立刻就完成執(zhí)行了。如下圖所示：

4 pg_stat_activity表定義

                  View "pg_catalog.pg_stat_activity"

Column	Type	Collation	Nullable	Default
------------------±-------------------------±----------±---------±--------
datid	oid
datname	name
pid	integer
usesysid	oid
usename	name
application_name	text
client_addr	inet
client_hostname	text
client_port	integer
backend_start	timestamp with time zone
xact_start	timestamp with time zone
query_start	timestamp with time zone
state_change	timestamp with time zone
wait_event_type	text
wait_event	text
state	text
backend_xid	xid
backend_xmin	xid
query	text
backend_type	text