PostgreSQL『ERROR: cannot execute SELECT FOR UPDATE』の原因と対処

ERROR:  cannot execute SELECT FOR UPDATE in a read-only transaction
ERROR:  cannot execute SELECT FOR UPDATE during recovery
ERROR:  FOR UPDATE is not allowed in subqueries
ERROR:  FOR UPDATE cannot be applied to a query with aggregate/grouping
ERROR:  relation "t" in FOR UPDATE clause not found in FROM clause

□ BEGIN READ ONLY / SET TRANSACTION READ ONLY / default_transaction_read_only=on
□ リードレプリカへ接続（pg_is_in_recovery()=true）
□ サブクエリやビュー内部でFOR UPDATEを記述（最外側SELECTでない）
□ GROUP BY/集約/UNION/INTERSECT/EXCEPT等で基底行が特定不能
□ JOIN時に FOR UPDATE OF の対象/別名の指定ミス
□ FDW/複雑ビュー/並列計画との相性問題（実行環境依存）

-- 読取専用か
SHOW transaction_read_only;           -- on なら READ ONLY
SHOW default_transaction_read_only;   -- 既定値

-- レプリカか
SELECT pg_is_in_recovery();           -- true ならレプリカ（FOR UPDATE不可）

-- psqlで接続情報
\conninfo

-- READ ONLYで開始してしまったケース
ROLLBACK;
BEGIN;                         -- または BEGIN READ WRITE;
SET TRANSACTION READ WRITE;
-- ここで SELECT ... FOR UPDATE を再実行

-- セッション既定を当面だけ変更
SET default_transaction_read_only = off;

-- レプリカ接続だった場合：writerへ接続先を切替
-- libpq 例：書込可能ノードを自動選別
-- postgresql://user@host1,host2/db?target_session_attrs=read-write

-- OK：最外側SELECTでFOR UPDATE、行の出所がt1に対応
SELECT t1.*
FROM t1
WHERE t1.status = 'ready'
FOR UPDATE;

-- OK：JOIN時は対象を限定（t1だけロック）
SELECT t1.*, t2.*
FROM t1 JOIN t2 ON t1.id = t2.t1_id
WHERE t1.status = 'ready'
FOR UPDATE OF t1;

-- NG：サブクエリ内のFOR UPDATE
SELECT * FROM (
  SELECT * FROM t1 WHERE status='ready' FOR UPDATE
) s;  -- ERROR: FOR UPDATE is not allowed in subqueries

-- NG：GROUP BY 直上
SELECT user_id, count(*) FROM logs
GROUP BY user_id
FOR UPDATE;  -- エラー

-- 対処：代表行を取りたいならウィンドウ関数やDISTINCT ONへ
WITH ranked AS (
  SELECT l.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) rn
  FROM logs l
)
SELECT * FROM ranked WHERE rn=1 FOR UPDATE;  -- 最外側で

-- 競合を待たずに取り出す（ジョブピック）
BEGIN;
WITH picked AS (
  SELECT id
  FROM jobs
  WHERE status='ready'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs j
SET status='running'
FROM picked p
WHERE j.id = p.id
RETURNING j.*;
COMMIT;

-- 即時エラーにしたいとき
SELECT * FROM accounts WHERE id=1 FOR UPDATE NOWAIT;

-- FROMで使っている別名を指定
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
FOR UPDATE OF o;     -- OK

-- NG：FROMにない名前
FOR UPDATE OF payments;  -- ERROR: relation ... not found in FROM clause

-- セッション単位で並列を避ける
SET max_parallel_workers_per_gather = 0;

-- またはクエリを書き換えて並列計画にならないよう抑制

-- 代替1：UPDATE ... RETURNING で直接更新と取得を一体化（writerで実行）
WITH cte AS (
  SELECT id FROM t WHERE picked=false
  ORDER BY id
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE t
SET picked=true
FROM cte
WHERE t.id = cte.id
RETURNING t.*;

-- 代替2：アドバイザリロック（プロセス間の調停）
SELECT pg_try_advisory_lock(42);  -- 取得できたら続行
-- ・・・処理・・・
SELECT pg_advisory_unlock(42);

-- 1) READ ONLYでの再現と解消
BEGIN READ ONLY;
SELECT * FROM t FOR UPDATE;
-- ERROR: cannot execute SELECT FOR UPDATE in a read-only transaction
ROLLBACK;

BEGIN;
SET TRANSACTION READ WRITE;
SELECT * FROM t FOR UPDATE;  -- OK
COMMIT;

-- 2) レプリカでの再現
SELECT pg_is_in_recovery();  -- true ならレプリカ
SELECT * FROM t FOR UPDATE;  -- ERROR: ... during recovery

-- 3) サブクエリ内でのNG
SELECT * FROM (SELECT * FROM t WHERE status='ready' FOR UPDATE) s;
-- ERROR: FOR UPDATE is not allowed in subqueries