Databases

\dnコマンドでスキーマ一覧を確認する PostgreSQLでスキーマ一覧を確認するには、psqlコマンドラインツールの\dnコマンドを使う。 \dn List of schemas Name | Owner --------+------------------- app | postgres public | pg_database_owner report | app_user sales | postgres (4 rows) pg_catalogやinformation_schemaなどのシステムスキーマは除外され、自分で作成したスキーマとpublicスキーマだけが表示される。

pg_hba.confの場所を探すコマンド(show hba_file) pg_hba.confはPostgreSQLのクライアント認証を設定するファイルで、どのホストからどのユーザーがどのデータベースに、どの認証方式で接続できるかを定義する。 postgresql.confと同様にOSやインストール方法によって配置場所が異なる。

psqlの\watchでクエリを繰り返し実行する psqlの\watchメタコマンドは、直前に実行したクエリを一定間隔で繰り返し実行する。実行中のクエリやテーブルの件数など、時間とともに変化する値を監視する場合に使う。

デッドロックとは 2つ以上のトランザクションが互いの保持するロックの解放を待ち合い、どちらも先に進めなくなった状態である。PostgreSQLはdeadlock_timeout(デフォルト1秒)を超えてロックを待ったときに待ち合わせの循環を検査し、循環を見つけるとトランザクションのひとつを中断する。

SELECT FOR UPDATEとは SELECT ... FOR UPDATEは、取得した行に対して排他的な行ロックを取得する構文である。ロックはトランザクションの終了まで保持され、他のトランザクションは同じ行に対するUPDATEやDELETE、SELECT FOR UPDATEで待たされる。

設定の反映方法は3通り PostgreSQLの設定を変更したあと、変更を反映する操作は設定項目ごとに異なる。 graph TD A[設定を変更] --> B{context} B -->|internal| C[変更不可] B -->|postmaster| D[設定ファイル + 再起動] B -->|sighup| E["設定ファイル + リロード"] B -->|superuser-backend / backend| F["設定ファイル + リロード<br/>反映は新規セッションから"] B -->|superuser / user| G["設定ファイル + リロード<br/>またはSETでセッション単位に変更"] postgresql.confを書き換えてリロードしただけでは反映されない設定もあるため、変更前に判別しておく。判別にはpg_settingsのcontextカラムを使用する。

pg_stat_user_tablesとは pg_stat_user_tablesは、テーブルごとのアクセス状況を集計するビューである。Seq Scan(全件走査)とIndex Scanのそれぞれの回数を確認できるため、インデックスが不足しているテーブルの発見に使える。

shared_buffersとwork_memの役割 PostgreSQLのメモリは大きく2種類ある。全プロセスで共有する領域と、バックエンドプロセスごとに確保する領域である。shared_buffersは前者、work_memは後者を制御するパラメータである。

SHOW ALLで設定値を一覧する PostgreSQLの全設定値を一覧するにはSHOW ALLコマンドを実行する。 SHOW ALL; name | setting | description ---------------------------------+---------+--------------------------------------------------------- application_name | psql | Sets the application name to be reported in statistics... archive_mode | off | Allows archiving of WAL files using archive_command. autovacuum | on | Starts the autovacuum subprocess. max_connections | 100 | Sets the maximum number of concurrent connections. shared_buffers | 16384 | Sets the number of shared memory buffers used by the ... work_mem | 4096 | Sets the maximum memory to be used for query workspaces. ・ ・ ・ (300 rows) 設定項目数は300件を超え(バージョンによって異なる)、name・setting・descriptionの3カラムのみが表示される。

そのままCHECK制約を追加する場合の問題 既存のテーブルにCHECK制約を追加するには、ALTER TABLE ... ADD CONSTRAINT ... CHECKを使用する。 ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0); この方法は、テーブル全体をスキャンして既存の全行が条件を満たすことを検証する。 検証中はテーブルに対してACCESS EXCLUSIVEロックを保持し続けるため、大規模なテーブルでは検証が終わるまでの間、参照・更新を含むすべてのアクセスがブロックされる。