開いた読み取り専用の PostgreSQL トランザクションが DROP TABLE をブロックするのは、その SELECT がトランザクションの終了まで ACCESS SHARE テーブルロックを保持するためです。削除には競合する ACCESS EXCLUSIVE ロックが必要なため、クエリが返された後も待機します。PostgreSQL: 明示的なロック

このガイドでは、Mac からこの待機を再現し、ブロックしているセッションを特定して、node-postgres でトランザクションを正しく終了する方法を説明します。合成した記事行のみを使用します。SQL は PostgreSQL 18 を対象としています。これは、この記事のために取得した公式の最新ドキュメントで扱われているバージョンです。バージョン固有の設定を適用する前に、サーバーのバージョンに対応したマニュアルを参照してください。

PostgreSQL のロックにおける読み取り専用とは何ですか？

READ ONLY は、トランザクションで実行できるコマンドを制限します。たとえば、通常のテーブルへの変更や DROP TABLE などのコマンドは禁止されます。ただし、読み取りがテーブルロックの対象外になるわけではありません。このシナリオでは、読み取り側と削除を試みるセッションは別々のトランザクションです。PostgreSQL: SET TRANSACTION

通常の SELECT は、参照するテーブルに対して自動的に ACCESS SHARE を取得します。DROP TABLE には、そのロックと競合する ACCESS EXCLUSIVE が必要です。ロックは通常、トランザクションが終了するまで保持されます。例外として、ロック取得前に設定したセーブポイントまでロールバックすると、ロックが解放される場合があります。以下の例ではセーブポイントを使いません。PostgreSQL: 明示的なロック

重要なのは、クエリの完了とトランザクションの完了を区別することです。ターミナルに記事の行が表示されたなら、クエリが返されたということです。その周囲のトランザクションも終了したか確認してください。psql では通常、自動コミットが有効ですが、明示的な BEGIN によって、終了処理を行うまでトランザクションが開いたままになります。PostgreSQL: psql

Mac で削除がブロックされる状態を再現するには？

Mac のターミナルで psql を使える状態にし、既存のローカル PostgreSQL 18 テストデータベースを使用します。同じデータベースに接続するターミナルタブを3つ開きます。それぞれを A、B、C とします。これらは別々の接続でなければなりません。エディターのペインを分けるだけでは、別接続になりません。

以下の接続コマンドでは、ローカルのテストデータベース名を postgres と想定しています。必要に応じて、既存のテストデータベース名に置き換えてください。各タブで実行します。-X オプションを付けると、個人用の psql 起動ファイルが読み込まれません。PostgreSQL: psql

psql -X -d postgres

各接続で自動コミットを明示的に有効にし、サーバーのバージョンを確認します。以下のブロックは順番に進める手順です。後続のブロックは、それより前に設定した内容やセッションの状態に依存します。

\set AUTOCOMMIT on
SHOW server_version;

合成した記事テーブルを作成する

タブ C でセットアップを一度実行します。まだ使われていないテーブル名を選んでください。public.article_lock_demo がすでに存在する場合は中止し、例全体で一貫して使う別の名前を選びます。このセットアップでは、既存のテーブルを削除しません。

CREATE TABLE public.article_lock_demo (
    id integer PRIMARY KEY,
    title text NOT NULL
);

INSERT INTO public.article_lock_demo (id, title)
VALUES
    (1, 'Choosing a notebook'),
    (2, 'Organizing a reading list');

読み取り側のトランザクションを開いたままにする

タブ A でこのブロックを実行し、プロンプトが表示された状態で接続を開いたままにします。まだコミットしないでください。

SET application_name = 'article_reader_demo';
BEGIN READ ONLY;
SHOW transaction_read_only;
SELECT id, title
FROM public.article_lock_demo
ORDER BY id;

transaction_read_only が on を表示し、その後に合成した2行が表示されるはずです。読み取りは完了していますが、明示的なトランザクションは開いたままです。

別の接続から削除を試みる

タブ B で以下のブロックを実行します。DROP TABLE を最後のステートメントとして送信し、その後に別のコマンドを追加しないでください。

SET application_name = 'article_drop_demo';
BEGIN;
SET LOCAL lock_timeout = '60s';
DROP TABLE public.article_lock_demo;

A が開いたままなら、削除は待機するはずです。例として設定した60秒の上限は、待機状態を調べる時間を確保するためのもので、一般的に推奨される設定ではありません。lock_timeout は、ロック取得の待機が長すぎるステートメントを中止します。また、ロック取得を試みるたびに個別に適用されます。PostgreSQL: クライアント接続のデフォルト設定

削除が返った後も、B の明示的なトランザクションを開いたままにします。後でロールバックすれば、削除が成功してもデモ用テーブルを保持できます。DROP TABLE を含むトランザクションをコミットすると、テーブルと関連するインデックスおよび制約が削除されます。PostgreSQL: DROP TABLE

どのセッションが削除をブロックしているか確認するには？

B が待機している間に、C で次のクエリを実行します。このクエリは2つのデモ用接続を特定し、各接続のロック要求をブロックしているセッションを PostgreSQL に問い合わせます。pg_blocking_pids() はブロックしているプロセス ID の配列を返し、ブロックされていない場合は空の配列を返します。PostgreSQL: システム情報関数と演算子

SELECT
    pid,
    application_name,
    state,
    xact_start,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    query
FROM pg_stat_activity
WHERE datname = current_database()
  AND application_name IN (
      'article_reader_demo',
      'article_drop_demo'
  )
ORDER BY application_name;

A は idle in transaction と表示されるはずです。トランザクションは開いていますが、クエリは実行していない状態です。B は待機中に active と表示され、待機イベント種別が Lock となることがあります。PostgreSQL のドキュメントでは、実行状態と待機イベントは独立した情報として説明されています。そのため、active だけではコマンドが進行中だとは判断できません。PostgreSQL: 累積統計システム

次に、実際のテーブルロック要求を調べます。A のトランザクションを終了する前に、C で実行してください。

SELECT
    a.application_name,
    l.pid,
    l.mode,
    l.granted
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.locktype = 'relation'
  AND l.database = (
      SELECT oid
      FROM pg_database
      WHERE datname = current_database()
  )
  AND l.relation = 'public.article_lock_demo'::regclass
ORDER BY a.application_name, l.mode;

以下は想定されるパターンの例であり、実際の実行結果ではありません。pg_locks では、granted = true はプロセスがロックを保持していることを意味し、false はそのロックを待っていることを意味します。PostgreSQL: pg_locks

セッション ロックモード granted 解釈
article_reader_demo AccessShareLock true 読み取り側がテーブルロックを保持している
article_drop_demo AccessExclusiveLock false 削除側がテーブルロックを待っている

この2つのクエリを診断の手順として併用します。まずブロック関係を確認し、次に対象テーブルの保持中および要求中のロックを調べます。セッションがアイドル状態であることだけでは十分な証拠になりません。ブロック関係とロック行を合わせて確認することで、そのセッションが今回の削除をブロックしていると特定できます。

B がすでにタイムアウトしている場合、待機中のロック要求は表示されなくなります。B で ROLLBACK; を実行し、A がまだ開いている間に B のトランザクションブロックをもう一度実行して、すぐに調べてください。

読み取り側を解放してデモを完了するには？

A で読み取り専用トランザクションを終了します。

COMMIT;

B がまだ待機中で、A だけがブロッカーであれば、削除が完了します。B で、合成テーブルを保持できるよう、デモ用の削除をすぐに取り消してください。

ROLLBACK;

A をコミットする前に B がタイムアウトした場合は、まず B で ROLLBACK; を実行します。A のトランザクションを終了した後、B のブロックをもう一度実行し、削除が成功したら再度 ROLLBACK; を実行します。これにより、どちらの結果になっても B のトランザクションを未完了のままにせずに済みます。

以下の判断表を使うと、トランザクションの有効期間に関する問題と、別の原因を区別できます。状態の表記は PostgreSQL のセッション状態に関するドキュメントに基づいています。PostgreSQL: 累積統計システム

状況 次の操作
A が idle in transaction で、B のブロッカー配列に A の PID が含まれる A の接続でトランザクションを終了する
A のトランザクション終了後も、B がブロッカー PID を報告する 残りのブロッカーを調べる
B がロックタイムアウトを報告する 再試行する前に、B の明示的なトランザクションをロールバックする
B が依存関係エラーを返す ロック待ちとして扱わず、依存関係を調べる
B がすぐに返る 意図した読み取り側のトランザクションがまだ開いていたか確認する

IF EXISTS はテーブルが存在しない場合に対応し、CASCADE は依存オブジェクトの削除方法を制御します。どちらもロック待ちに関するオプションではありません。この演習では依存関係に関するデフォルトの動作を維持してください。公式の DROP TABLE 構文に CONCURRENTLY という形式はありません。PostgreSQL: DROP TABLE

node-postgres で読み取り専用トランザクションを終了するには？

node-postgres では、BEGIN、トランザクション内のすべてのステートメント、および最後の COMMIT または ROLLBACK に同じ貸し出し済みクライアントを使用します。公式のトランザクションに関するドキュメントでは、トランザクションに pool.query() を使うべきではないと明記されています。ステートメントが別々のクライアントに振り分けられる可能性があるためです。node-postgres: トランザクション

トランザクションの完了とクライアントの解放は、アプリケーション上の別々の手順として扱います。client.release() は貸し出し済みのクライアントをプールに戻します。この呼び出しの前にトランザクションを終了してください。ロールバックに失敗した場合、プール API では release() に truthy な引数を渡すことで、クライアントを破棄できます。node-postgres: Pool API

この完全なプログラムでは、手順で作成した合成テーブルをそのまま使います。ES モジュールと AggregateError をサポートする Node.js ランタイムをインストールし、作業用プロジェクトディレクトリで pg をインストールしてください。

npm install pg

以下を read-articles.mjs として保存します。接続先は、psql で使ったものと同じローカルテストデータベースに設定してください。node-postgres は PGDATABASE を含む PostgreSQL 接続用環境変数をサポートしています。node-postgres: 接続

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ connectionTimeoutMillis: 5000 });

async function main() {
  try {
    const client = await pool.connect();
    let discard = false;

    try {
      await client.query('BEGIN READ ONLY');
      const result = await client.query(
        'SELECT id, title FROM public.article_lock_demo ORDER BY id'
      );
      await client.query('COMMIT');
      console.table(result.rows);
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discard = true;
        throw new AggregateError(
          [error, rollbackError],
          'Query and rollback failed'
        );
      }
      throw error;
    } finally {
      client.release(discard);
    }
  } finally {
    await pool.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

PGDATABASE=postgres node read-articles.mjs

このプログラムは行を表示する前にコミットし、エラー時にはロールバックし、どちらの場合もクライアントを解放します。JavaScript の構文は Node.js で確認しました。調査環境では PostgreSQL が利用できなかったため、データベースの手順はドキュメントと照らして確認しましたが、実際には実行していません。

単独の読み取りであれば、pool.query() がドキュメントに記載された簡便な方法で、クライアントを手動で貸し出す必要がありません。タスクで明示的なトランザクションが必要な場合はそれを選び、無関係なファイル処理やデスクトップ操作はその有効期間の外で行ってください。node-postgres: Pool API

終了していない読み取り側には、どのタイムアウトを使うべきですか？

何が待機しているかに応じてタイムアウトを使い分けます。lock_timeout は、削除がロック取得を待つ時間を制限します。statement_timeout はステートメントの実行時間を制限するもので、読み取りが成功した後にアプリケーションがコマンド送信を停止しただけでトランザクションを終了するものではありません。idle_in_transaction_session_timeout は、開いたトランザクション内でアイドル状態のままになっているセッションを特に終了させます。これらは PostgreSQL 18 の設定です。アプリケーションで想定される処理に合わせて時間を選んでください。PostgreSQL: クライアント接続のデフォルト設定

この作業で確認すべきなのは、読み取り側のトランザクションが終了したかどうかです。ブロッカーの PID を確認し、テーブルロックを調べて、所有する接続でトランザクションを終了してください。node-postgres では、その最後のトランザクションコマンドを成功時とエラー時の通常の処理経路に含めることで、読み取りが完了した後に次のテーブル削除が待たされるのを防げます。

出典