psycopg2ライブラリを使ってPostgreSQLを参照している際に不思議な事象に見舞われたのでメモしておきます。

環境

PostgreSQL: 10.4
Python: 3.6.6
psycopg2: 2.8.2 (dt dec pq3 ext lo64)
pandas: 0.24.2

準備

PostgreSQLに検証用テーブルを作成しておきます。
monitoringスキーマにlock_testという1カラムのテーブルを作ります。


create table monitoring.lock_test
as
select generate_series(1,3) as num

検証実施

psycopg2のconnectionのclose方法によってconnectionオブジェクトがどう変わるのか？
実際にDB側でロック待ちが発生するのか？を検証していきます。

ライブラリのインポートと接続情報の設定


import psycopg2
import pandas as pd

# PostgreSQLの接続情報dict作成
param = {
    'port': '5432',
    'user': '*****',
    'password': '*****',
    'host': 'localhost',
    'dbname': '*****'
}

DBがロックされる悪い例

以下を実行した場合、connectionオブジェクトがcloseしていないためDBがロックされます。


# 悪い例(connectionオブジェクトをcloseしない場合)
conn = psycopg2.connect(**param)
query='''
select * from monitoring.lock_test;
'''
pd.read_sql(query,con=conn)

実際にDB側でロック待ちが発生していないか確認します。
以下のSQLを実行します。

SELECT l.pid, db.datname, c.relname, l.locktype, l.mode
FROM pg_locks l
        LEFT JOIN pg_class c ON l.relation=c.relfilenode
        LEFT JOIN pg_database db ON l.database = db.oid
ORDER BY l.pid;

実行結果：

想定通り、lock_testテーブルでExclusivelockになっています。
つまり、 lock_testテーブルに関してDB側でロック待ちが発生しています。
次に、connectionオブジェクトの状態も確認してみます。

print(conn)

実行結果：

closedフラグが0になっており、connectionオブジェクトがcloseされていないことが分かります。
（connectionオブジェクトをcloseしていないので当然なのですが、、、）

では、明示的にconnectionオブジェクトをcloseするとどうなるのか確認してみます。

conn.close()
print(conn)

`closed`フラグが1になっており、`connection`オブジェクトが`close`されています。実際にDB側でロック待ちが解消していることを確認してみます。

先ほどの`Exclusivelock`が消えており、ロック待ちが解消されていることがわかります。

DBがロックされない良い例

いやいや普通はwith構文を使うだろう？
ってことでwith構文でconnectionオブジェクトをcloseしてみます。

# with構文でconnectionを勝手にcloseさせる
with psycopg2.connect(**param) as conn:
    query='''
    select * from monitoring.lock_test;
    '''
    pd.read_sql(query,con=conn)

print(conn)

実行結果：

なんと、connectionオブジェクトがcloseされていません。
これはどういうことだろうか？実際にDB側でロック待ちが発生しているかを確認してみます。

ロック待ちは発生していません、、、
connectionオブジェクトがcloseされているということでしょうか？
実際、lock_testテーブルをdropすることもできたので、ロック待ちは本当に発生していないようです。
connectionオブジェクトのclosedフラグは当てにならないのだろうか、、、。

まとめ

closedフラグが1にならない原因はよく分かりませんが、psycopg2のconnectionオブジェクトはwith構文で自動的に閉じるべきだと思います。
with構文を使わずにcloseを実行しても良いのですが、closeし忘れるとDBにロック待ちが発生してしまうので、リスク大です。
（自戒の念を込めて、、、）