PostgreSQL『transaction is read-only』エラーの原因と対処

アプリやpsqlでINSERT/UPDATE/DELETE/DDLを実行したときに「transaction is read-only」または「cannot execute … in a read-only transaction」が出る状況の原因を体系的に整理し、確認コマンドと安全な回復手順をまとめた。読取専用トランザクション/セッション、リードレプリカ接続、アプリ側のreadOnly指定、ユーザー/DB既定設定、フェイルオーバー直後のリカバリ中などを網羅する。すぐ使える診断チェックリストとサンプルSQL付き。

エラーの意味と発生メッセージ例

・「transaction is read-only」= 現在のトランザクション（あるいはセッション）が書き込み禁止モード
・代表的な表示例

ERROR:  transaction is read-only
ERROR:  cannot execute INSERT in a read-only transaction
ERROR:  cannot execute CREATE TABLE in a read-only transaction
ERROR:  cannot set transaction read-write mode during recovery

典型的な発生条件（まずここを洗い出す）

・BEGIN READ ONLY / START TRANSACTION READ ONLY を実行している
・SET TRANSACTION READ ONLY を先に実行している
・default_transaction_read_only が on（セッション/ユーザー/DB/クラスタのどこかで有効）
・リードレプリカ（pg_is_in_recovery() = true）に接続している
・フェイルオーバー/復旧直後でプライマリだと思ったが実はリカバリ中
・ORM/フレームワーク設定（例: Spring @Transactional(readOnly=true)）が有効
・クラウドの「リーダーエンドポイント」へ接続（RDS/Aurora/Cloud SQL等）
・接続先選択でwriter優先を強制する設定がない（target_session_attrs未使用）

まず実行：現在の状態を確認するSQLとpsqlコマンド

-- セッション/トランザクションの読取専用フラグ
SHOW transaction_read_only;           -- on/off（今のTxに対する見え方）
SHOW default_transaction_read_only;   -- 既定値（次のTxに影響）

-- サーバがリカバリ中か（=通常レプリカ）
SELECT pg_is_in_recovery();           -- trueなら書き込み不可

-- psqlなら接続先を表示
\conninfo

単発で解除：書込み可能なトランザクションを開始する

・既にREAD ONLYでBEGINしてしまった場合は一度終了してやり直す

-- 誤ってREAD ONLYで開始していた
ROLLBACK;  -- いったん終わらせる

-- 正しくREAD WRITEで開始（SET TRANSACTIONはBEGIN直後のみ）
BEGIN;
SET TRANSACTION READ WRITE;
-- 以降にINSERT/UPDATE/DDLを実行
COMMIT;

・セッションの既定を当面だけオフにする

SET default_transaction_read_only = off;  -- 現在のセッションだけ

・pg_is_in_recovery() = true の場合は下記の通り失敗する（=リードレプリカでは不可）

BEGIN;
SET TRANSACTION READ WRITE;
-- ERROR:  cannot set transaction read-write mode during recovery
ROLLBACK;

恒久対策：ユーザー/データベースの既定設定を修正する

・アプリ用ユーザーの既定を直す

ALTER ROLE appuser SET default_transaction_read_only = off;

・データベース単位の既定を直す

ALTER DATABASE mydb SET default_transaction_read_only = off;

・クラスタ設定で強制している場合（まれ）

-- postgresql.conf 例（サーバ再起動/リロードが必要な場合あり）
# default_transaction_read_only = off

接続先がリードレプリカのときの回避策

・「書込み可能なプライマリ/ライター」へつなぐことが根本
・複数ホスト指定時は libpq の接続属性で「read-write」を要求

# 複数ホストに対して書込み可能ノードを自動選別
postgresql://appuser@db1,db2,db3/mydb?target_session_attrs=read-write

・クラウドのエンドポイント選択を見直す（writer/primaryを使用）
・接続プール/プロキシ（PgBouncer/pgpool/HAProxy等）の向き先をwriterへ

アプリ/ORMのreadOnly指定が原因のケース

・Spring

@Transactional(readOnly = true)  -- これがあるとSET TRANSACTION READ ONLYに相当

・対処

-- 書込み系メソッドではreadOnly=falseにするか、アノテーションを分離
@Transactional(readOnly = false)
public void createOrder(...) { ... }

・他のORMでも同様に「読取専用トランザクション」オプションの有無を点検

代表的な再現と解消のミニチュートリアル

-- 再現（READ ONLYでBEGINして書込み）
BEGIN READ ONLY;
INSERT INTO demo(id) VALUES (1);
-- ERROR:  cannot execute INSERT in a read-only transaction
ROLLBACK;

-- 解消（READ WRITEでBEGINし直す）
BEGIN;
SET TRANSACTION READ WRITE;  -- BEGIN直後に限る
INSERT INTO demo(id) VALUES (1);
COMMIT;

リカバリ/フェイルオーバー直後に発生するときの確認順

・pg_is_in_recovery() を見る（trueなら書込み不可）
・プライマリ昇格が完了しているか/接続先DNSが切替済みかを運用側で確認
・アプリの接続先キャッシュ/プールをクリア（古い接続が残ると継続発生）
・target_session_attrs=read-write で誤接続を防止

DDLや一時テーブルでも起きる点に注意

・CREATE/ALTER/DROP/CREATE INDEX/CREATE TEMP TABLE も同じく失敗

CREATE TEMP TABLE t (id int);
-- ERROR:  cannot execute CREATE TABLE in a read-only transaction

・「読み取りだけのはず」と思っても、内部で一時領域やカタログ更新が必要な操作は弾かれる

実運用向け：診断チェックリスト（上から順に実施）

・SHOW transaction_read_only / SHOW default_transaction_read_only
・SELECT pg_is_in_recovery()
・\conninfo で接続先（ホスト/ポート/DB/SSL）
・アプリ設定のreadOnlyフラグやトランザクション境界
・接続文字列に target_session_attrs=read-write を付与
・ユーザー/DB/クラスタの既定値（ALTER ROLE/ALTER DATABASE/postgresql.conf）
・クラウドのwriter/endpoints設定、フェイルオーバーやメンテの影響

トラブル再発防止のベストプラクティス

・書込み系は必ず独立トランザクションでBEGIN→SET TRANSACTION READ WRITE
・アプリの「読取専用」設定を機能別に厳密化（R/W分離）
・複数ホスト接続では target_session_attrs=read-write を標準化
・監視に pg_is_in_recovery() と SHOW 値を組み込み、誤接続を検知
・クラウドではwriter/readerのDNSを明確に使い分ける

検証用サンプルスキーマと一連のコマンド

-- 検証テーブル
CREATE TABLE IF NOT EXISTS demo (id int primary key);

-- 読取専用セッション（意図的にエラーを出す）
SET default_transaction_read_only = on;
BEGIN;
INSERT INTO demo VALUES (100);
-- ERROR:  cannot execute INSERT in a read-only transaction
ROLLBACK;

-- 解除して成功パス
SET default_transaction_read_only = off;
BEGIN;
SET TRANSACTION READ WRITE;  -- BEGIN直後のみ有効
INSERT INTO demo VALUES (100);
COMMIT;

まとめ：最短復旧の行動手順

・1) SHOW と pg_is_in_recovery() で状態を特定
・2) レプリカならwriterへ接続先変更（target_session_attrs=read-write推奨）
・3) 読取専用TxならROLLBACK→BEGIN→SET TRANSACTION READ WRITE
・4) 既定がonなら ALTER ROLE/ALTER DATABASE/SET で恒久修正
・5) アプリ設定のreadOnlyを機能ごとに見直し、再発を防止