PostgreSQLでプロセスとロック状況を確認する

## 実行中のSQLを確認する。
SELECT * FROM pg_stat_activity;

## 実行中のSQLを確認する
## スロークエリとか主に見る分だけ
SELECT pid, query_start, substr(query, 0, 80) AS query FROM pg_stat_activity WHERE state='active' ORDER BY query_start;

## ロック状況の確認
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;

## AccessExclusiveLockロックがかかっているか
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
WHERE l.mode = 'AccessExclusiveLock'
ORDER BY l.pid;

## Railsのマイグレーションでロックをかけない呪文
## デフォだとSELECTも通さないAccessExclusiveLockがかかる
class AddStatusToUsers < ActiveRecord::Migration
  disable_ddl_transaction!

  def up
    # 略
    Users.find_each do |user|
      user.update_columns(status: 'xxx')
    end
  end

  def down
    # 略
  end
end

## インデックス追加中にロックかけない
## (concurrentlyオプションを有効にする)
## https://thoughtbot.com/blog/how-to-create-postgres-indexes-concurrently-in
class AddIndexToAsksActive < ActiveRecord::Migration
  disable_ddl_transaction!

  def change
    add_index :asks, :active, algorithm: :concurrently
  end
end

メモ

PostgreSQL×Rails環境でのマイグレーションで、バカでかいテーブルに対して、add_column + eachまわしてupdate ということをやると、こうなる。

・add_columnsで、AccessExclusiveLockかかる。
・AccessExclusiveLockの中でeachでUPDATE走る。
・AccessExclusiveLockはSELECTも通さないロックなので、
　該当テーブルに対するSELECTがすべて待ちになる。
・待ちのSELECTでDBプロセス食い尽くす。
・DBが応答なくなる。
・障害発生 :unko: