TwitterFacebookRSS Feed

PostgreSQLのインデックス名などの長さ！

こんにちは、Anti-Pattern Inc.の塚本です。

PostgreSQLのインデックス名など識別子の長さについて調べたメモです。

usersテーブルに長いインデックス名を作成します。

CREATE UNIQUE INDEX unique_index_looooooooooooooooooooooooooooooooooong__users_name_email_key
  ON users (name, email) 
  WHERE deleted_at IS NULL;

成功しますが、”識別子が切り捨てられます”とメッセージがでます。

test_db=> CREATE UNIQUE INDEX unique_index_looooooooooooooooooooooooooooooooooong__users_name_email_key
test_db->   ON users (name, email) 
test_db->   WHERE deleted_at IS NULL;
NOTICE:  identifier "unique_index_looooooooooooooooooooooooooooooooooong__users_name_email_key" will be truncated to "unique_index_looooooooooooooooooooooooooooooooooong__users_name"
CREATE INDEX

統計情報コレクタ（pg_stat_user_indexes）で、今作成したインデックスを確認してみると、切り捨てられたインデックス名で作成されてます。

  test_db=> select * from pg_stat_user_indexes where indexrelname = 'unique_index_looooooooooooooooooooooooooooooooooong__users_name';
  -[ RECORD 1 ]-+--------------------------------------------------------
  relid         | 16528
  indexrelid    | 4934005
  schemaname    | engineed
  relname       | users
  indexrelname  | unique_index_looooooooooooooooooooooooooooooooooong__users_name
  idx_scan      | 0
  idx_tup_read  | 0
  idx_tup_fetch | 0

indexrelnameのtypeがnameとなっているのが分かります

  test_db=> \d+ pg_stat_user_indexes
                       View "pg_catalog.pg_stat_user_indexes"
      Column     |  Type  | Collation | Nullable | Default | Storage | Description 
  ---------------+--------+-----------+----------+---------+---------+---
   relid         | oid    |           |          |         | plain   | 
   indexrelid    | oid    |           |          |         | plain   | 
   schemaname    | name   |           |          |         | plain   | 
   relname       | name   |           |          |         | plain   | 
   indexrelname  | name   |           |          |         | plain   | 
   idx_scan      | bigint |           |          |         | plain   | 
   idx_tup_read  | bigint |           |          |         | plain   | 
   idx_tup_fetch | bigint |           |          |         | plain   | 
-抜粋-

pg_typeカタログでデータ型の情報がわかるので、nameを確認すると、typlenが64になってます。

https://www.postgresql.jp/document/12/html/catalog-pg-type.html

  test_db=> select * from pg_type where typname = 'name';
  -[ RECORD 1 ]--+---------
  typname        | name
  typnamespace   | 11
  typowner       | 10
  typlen         | 64
  typbyval       | f
  typtype        | b
-抜粋-

PostgreSQLマニュアル "4.1.1. 識別子とキーワード"

システムはNAMEDATALEN-1バイトより長い識別子を使いません。 より長い名前をコマンドで書くことはできますが、短く切られてしまいます。 デフォルトではNAMEDATALENは64なので、識別子は最長で63バイトです。 この制限が問題になる場合は、src/include/pg_config_manual.h内のNAMEDATALEN定数の値を変更して増やすことができます。

https://www.postgresql.jp/document/12/html/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

PostgreSQLマニュアル "19.15. 設定済みのオプション"

max_identifier_length (integer)
最長の識別子の長さを報告します。 サーバ構築時のNAMEDATALENの値より一つ少なく設定されます。 デフォルトのNAMEDATALENの値は64ですので、デフォルトのmax_identifier_lengthは63バイトで、マルチバイト符号化方式を使用している場合、63文字以下になることがあります。

https://www.postgresql.jp/document/12/html/runtime-config-preset.html

NAMEDATALEN定数を変えてリコンパイルすると、pg_typeのnameレコードのtyplenが変更できそうです。

事の発端は、外部参照キー名称をこのように付けてました。（正確にはツールで自動生成してました）
"FK" ＋ "＿" ＋ 外部参照するテーブル名 ＋ "＿" ＋ "TO" ＋ "＿" ＋ 自テーブル名
インデックス名はこんな感じです。
テーブル名 ＋ "＿" ＋ カラム名 ＋ "＿" ＋ "key"

設計中のテーブル名が長いため、設定したインデックス名、外部参照キー名が切り捨てられていることに気づき・・・。

nameという型を知ったので、勉強になった点はよかった！！

でも長いテーブル名はどうしようかな・・・見直そうかな。