PostgreSQL 遞歸死循環案例及解法

postgresql 提供的遞歸文法是很棒的，例如可用來解決樹形查詢的問題，解決oracle使用者 connect by的文法相容性。

請參考

<a href="https://yq.aliyun.com/articles/54657">https://yq.aliyun.com/articles/54657</a>

但是如果參與遞歸查詢的資料集有問題，例如資料打結的問題。則會導緻遞歸死循環，可能導緻臨時檔案暴增，把空間占滿，影響業務。

假設c1,c2是上下級關系，c2是c1的上級id。

建立測試表如下

插入一組測試資料，其中(1,1,'test')是個結，如果用遞歸查詢的話，會導緻無法退出循環。

遞歸查詢，從c1=9開始往上檢索，到1之後會一直往下走，無法終結。

可以在資料庫的臨時檔案目錄，看到不停增長的臨時檔案。

臨時檔案相關的資料庫參數介紹

看完以上幾個參數，大家應該心裡有數了。

通過設定temp_file_limit即可限制目前會話允許使用的最大臨時空間。

測試

手工退出剛才的死循環query

query退出後才記錄臨時檔案的日志，社群版本的問題，沒有階段性記錄臨時空間的使用

設定會話的臨時檔案使用為10mb，繼續測試，可以看到效果很明顯

死循環的問題解決了

建議可以動态設定temp_file_limit，根據實際的剩餘空間設定回報機制，保證有足夠的剩餘空間，不至于temp檔案把空間全部撐爆。

可以将會話級别的臨時空間限制，改為分組限制。

例如group a 允許使用100mb，group b允許使用1gb。

又或者是使用者或資料庫級别的限制。

借鑒greenplum的resource queue的管理手段，把資源控制做起來也是一種方法。

《greenplum 資源隔離的原理與源碼分析》

<a href="https://yq.aliyun.com/articles/57763">https://yq.aliyun.com/articles/57763</a>

階段性的記錄臨時檔案的日志，而不是query結束時記錄

使用者使用遞歸語句時一定要注意防止死循環，通過設定會話級别的temp_file_limit可以預防，還有一種方法是使用pg_hint_plan，在語句中使用hint，例如：

臨時檔案會在query結束後自動清理。

資料庫啟動時，startup程序也會清理temp檔案。

祝大家玩得開心，歡迎随時來 阿裡雲促膝長談 業務需求 ，恭候光臨。

阿裡雲的小夥伴們加油，努力做 最貼地氣的雲資料庫 。