###### tags: `小講堂` `COSCUP` `COSCUP2020` # Connect to PostgreSQL With Different Programming Language - CHIN SHEN FANG {%youtube bmkOXSYbgv8 %} --- 因為很多人沒有這個習慣,所以希望新手在看到這篇時可以思考利弊,養成好習慣。 補充一下建議在開始進行 Transaction 的時候,自己可以評估正常情況下的執行時間,有三個參數可以考慮設定: 1. [statement_timeout](https://docs.postgresql.tw/server-administration/server-configuration/client-connection-defaults#statement_timeout-integer) - 查詢預期的最長回應時間,可作為查詢作業的停損點,超時可能代表查詢複雜度過高。 2. [lock_timeout](https://docs.postgresql.tw/server-administration/server-configuration/client-connection-defaults#lock_timeout-integer) - 更新資料時取得鎖定的最長回應時間,超時可能表示資料處理有衝突,儘早 ROLLBACK 反而是好事。 3. [idle_in_transaction_session_timeout](https://docs.postgresql.tw/server-administration/server-configuration/client-connection-defaults#idle_in_transaction_session_timeout-integer) - 如果你自己的 transaction 在時限內「閒置」而沒有處理完,就會自動終止交易,釋放所有 LOCK。是保護他人的方式。 每一個查詢都不可能是無限的執行時間,在多人交易的執行環境中,保持可預期的執行時間等同於保持了資料庫系統的穩定性。而當執行時間超過預期時,通常是產生了意料之外的錯誤,應該儘早讓系統回報錯誤,儘快修正問題。 ## statement_timeout 範例如下,可以儘早發現錯誤: ```sql= postgres=# \timing on Timing is on. postgres=# SET statement_timeout=3000; SET Time: 0.424 ms postgres=# SELECT pg_sleep(5); ERROR: canceling statement due to statement timeout Time: 3002.658 ms (00:03.003) ``` ## lock_timeout lock_timeout 自己一個人的時候可能沒什麼感覺,但在大量資料庫連線時,可以節省等待的時間。 兩個不同連線的情況,其共同存取同一TABLE: ```sql= CREATE TABLE test (id int, value text); INSERT INTO test (id, value) VALUES (1, 'abc'); ``` - Process A ```sql= BEGIN; SET lock_timeout=3000; SELECT * FROM test WHERE id=1 FOR UPDATE; -- Process B is beginning the transaction. -- Process A is doing some application flows. UPDATE test SET value='def' WHERE id=1; COMMIT; ``` - Process B (另開一個連線測試) 如果在執行 SELECT 時,Process A 一直都沒有辦法執行到 COMMIT 的話: ```sql= SELECT * FROM test WHERE id=1 FOR UPDATE; ERROR: canceling statement due to lock timeout CONTEXT: while locking tuple (0,2) in relation "test" ``` 超過 3 秒就會回報錯誤了。 ## idle_in_transaction_session_timeout 避免不小心造成別人 lock_timeout 的友善處理方式是: ```sql= BEGIN; SET lock_timeout=3000; SET idle_in_transaction_session_timeout=3000 SELECT * FROM test WHERE id=1 FOR UPDATE; UPDATE test SET value='def' WHERE id=1; COMMIT; ``` 你預期整個 Transaction 「閒置」不應該超過 3 秒,也就是你都沒有在資料庫裡作業,可能是應用邏輯出了問題。如果超時就請自動幫我結束 Transaction,已經建立的 LOCK 都會解開,「我 OK,你先 RUN」。資料會 ROLLBACK,請不用擔心。 ## 保護自己,善待他人 手冊上雖然是寫不應該在 postgresql.conf 中設定全域參數,但就 DBA 的角度來說,如果可以協助團隊自律,這也是一個不能捨棄的管理策略之一。