###### 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 的角度來說,如果可以協助團隊自律,這也是一個不能捨棄的管理策略之一。