PostgreSQLのシーケンス操作について触って学ぶ

# はじめに

Gakken LEAPで働いていますkoboriです。普段はRuby on Railsを使ったWeb APIの開発を主な業務としています。日頃の開発業務では、ActiveRecordに助けられ、RDBMSの機能を強く意識せずに開発を進めることができていると感じています。しかし一方で、ミドルウェアに対する自身の解像度の低さに課題感を覚えています。そこで、ミドルウェアへの理解の一貫として、PostgreSQLのシーケンス操作について、触りつつドキュメントと照らし合わせて挙動を確かめていき、記事にしていくことにしました。

# 準備

## バージョン確認

まず始めに、PostgreSQLサーバを準備します。今回はローカル環境でDockerコンテナを利用しています。(本旨から逸れるため詳細は割愛します)
今回利用するPostgreSQLサーバのバージョンは下記の通りです。

```SQL
SHOW server_version;
```

| server_version               |
|------------------------------|
|16.6 (Debian 16.6-1.pgdg120+1)|

サーバのバージョンの確認方法はいくつかあるようですが、記事閲覧時の視認性の高さから、[文字列のバージョン情報を返すサーバオプション](https://www.postgresql.jp/docs/16/runtime-config-preset.html) を使用しました。

## シーケンスの作成

本稿の中でINSERT文の実行も行うため、SERIAL型のカラムを1つ持つテーブルを作成しておきます。

```SQL
CREATE TABLE mytable (
    id SERIAL
);
```

[ドキュメント](https://www.postgresql.jp/document/16/html/datatype-numeric.html#DATATYPE-SERIAL)に記載の通り、上記のCREATE TABLEは下記のSQLと等価になります。
`mytable_id_seq` という名前のシーケンスが作成されたことが分かりました。

```SQL
CREATE SEQUENCE mytable_id_seq AS integer;
CREATE TABLE mytable (
    id integer NOT NULL DEFAULT nextval('mytable_id_seq')
);
ALTER SEQUENCE mytable_id_seq OWNED BY mytable.id;
```

## シーケンスの確認

先ほど作成された `mytable_id_seq` という名前のシーケンスについて確認します。

```SQL
SELECT * FROM mytable_id_seq;
```

|last_value|log_cnt|is_called|
|----------|-------|---------|
|1         |0      |f        |

last_value, log_cnt, is_calledの3つのカラムを持つテーブルのようなオブジェクトが作成されたことが分かります。各カラムの意味については、後ほど見ていきます。

次に、シーケンス名が分からない場合に、シーケンスの名前やその他の情報を一覧として取得する方法について見ていきます。
まず初めに、システムカタログのひとつである [pg_class](https://www.postgresql.jp/docs/16/catalog-pg-class.html) を参照します。ドキュメントに記載の通り、`relkind` が 'S' のレコードを取得することで、シーケンス名の一覧を取得できます。
また、先ほど、「テーブルのようなオブジェクト」と書きましたが、シーケンスはリレーションの種別のひとつであることが分かりました。
しかし、`pg_class` には、シーケンス以外のリレーションも含まれているため、シーケンス名以外に欲しい情報を取得することができません。

```SQL
SELECT relname, oid FROM pg_class WHERE relkind = 'S';
```

|relname       |oid  |
|--------------|-----|
|mytable_id_seq|16394|

同じくシステムカタログのひとつである [pg_sequence](https://www.postgresql.jp/docs/16/catalog-pg-sequence.html)も見てみます。先ほど、`pg_class` で取得した `oid` を使い、シーケンスの情報を取得することができます。シーケンスの開始値、増分値、最大値、最小値などの情報が格納されていることが分かります。

```SQL
SELECT * FROM pg_sequence WHERE seqrelid = 16394;
```

|seqrelid|seqtypid|seqstart|seqincrement|seqmax    |seqmin|seqcache|seqcycle|
|--------|--------|--------|------------|----------|------|--------|--------|
|16394   |23      |1       |1           |2147483647|1     |1       |f       |

次に、`pg_class` と `pg_sequence` を統合した情報が格納されているシステムビューのひとつである[pg_sequences](https://www.postgresql.jp/document/16/html/view-pg-sequences.html)を見てみます。`pg_sequence`と名前が似ているので、間違えぬよう注意する必要があります。
下記のサンプルクエリでは、情報を絞って出力していますが、シーケンス名、シーケンスの開始値、増分値、最大値、最小値などの情報を簡単に取得することができることが分かります。

```SQL
SELECT pgs.sequencename, pgs.start_value, pgs.min_value, pgs.max_value, pgs.increment_by, pgs.cycle, pgs.last_value FROM pg_sequences pgs;
```

|sequencename  |start_value|min_value|max_value |increment_by|cycle|last_value|
|--------------|-----------|---------|----------|------------|-----|----------|
|mytable_id_seq|1          |1        |2147483647|1           |f    |          |

# シーケンスの操作

ここからは、作成したシーケンスに対する操作を行っていきます。

## シーケンス操作関数

PostgreSQLでは、[シーケンス操作関数](https://www.postgresql.jp/docs/16/functions-sequence.html) が用意されています。シーケンス操作関数のうち、シーケンスを更新することができる、`nextval`, `setval` について順番見ていきます。

### nextval

`nextval` は、シーケンスを次の値に進め、その値を返す関数で、下記のように呼び出すことができます。引数には、先ほど作成したシーケンス名を指定しています。
実行結果は、`1` が返ってきました。

```SQL
SELECT nextval('mytable_id_seq');
```

|nextval|
|-------|
|1      |

次に、`nextval` を実行した後の、シーケンスの情報を確認してみると、last_valueの値は変わらず、is_calledがtrueになったことが分かります。

```SQL
SELECT * FROM mytable_id_seq;
```

|last_value|log_cnt|is_called|
|----------|-------|---------|
|1         |32     |t        |

シーケンスをもうひとつ進めてみます。シーケンスの増分値は1に設定されていましたので、次の値は `2` が返されます。

```SQL
SELECT nextval('mytable_id_seq');
```

|nextval|
|-------|
|2      |

シーケンスの情報を確認すると、last_valueが2になり、is_calledがtrueのままであることが分かります。

```SQL
SELECT * FROM mytable_id_seq;
```

|last_value|log_cnt|is_called|
|----------|-------|---------|
|2         |31     |t        |

ここまでの操作から、下記の2点の挙動を確認することができました。

- is_called: falseの場合、last_valueの値を返し、is_calledをtrueに更新する
- is_called: trueの場合、last_valueの値をシーケンスの増分値だけ増加させて返し、is_calledはtrueのまま

次に、シーケンスが設定されたカラムに値を指定せずにINSERT文を実行すると、id: 3のレコードが作成されていることが分かります。

```SQL
INSERT INTO mytable DEFAULT VALUES;
SELECT * FROM mytable;
```

|id|
|--|
|3 |

また、シーケンスの情報を確認してみると、カラムのDEFAULTとして設定された `nextval('mytable_id_seq')` が機能していることが分かります。

```SQL
SELECT * FROM mytable_id_seq;
```

|last_value|log_cnt|is_called|
|----------|-------|---------|
|3         |30     |t        |

シーケンスが設定されたカラムに値を指定するINSERT文も実行してみます。id: 4のレコードが作成されました。

```SQL
INSERT INTO mytable (id) VALUES (4);
SELECT * FROM mytable;
```

|id|
|--|
|3 |
|4 |

シーケンスの情報を確認すると、nextvalが実行されていないことが分かります。次にidを指定せずにINSERT文を実行する場合、id: 4が採番されて既存のレコードと値が重複します。

```SQL
SELECT * FROM mytable_id_seq;
```

|last_value|log_cnt|is_called|
|----------|-------|---------|
|3         |30     |t        |

### setval

次に、シーケンスの現在位置を任意に設定できる `setval` について見ていきます。`setval` には、引数を3つ渡すことができ、シーケンス名と、設定したい位置、is_calledを指定することができます。このうち、is_calledのみがoptionalであり、defaultはtrueとなっています。
先ほど実行した、idが明示的に指定されたINSERT文によるシーケンスの不整合を解消するために、`setval` を実行してみます。

```SQL
SELECT setval('mytable_id_seq', 4);
```

シーケンスの情報を確認すると、last_valueの値が更新されたことが分かります。

```SQL
SELECT * FROM mytable_id_seq;
```

|last_value|log_cnt|is_called|
|----------|-------|---------|
|4         |0      |t        |

idを指定しないINSERT文を実行してみると、id: 5が採番されていることが分かります。

```SQL
INSERT INTO mytable DEFAULT VALUES;
SELECT * FROM mytable;
```

|id|
|--|
|3 |
|4 |
|5 |

### log_cntについて

ここまで、ドキュメントを参照しつつシーケンス操作関数を実行して、`last_value` と `is_called` について確認してきました。ですが、`log_cnt` については、ドキュメント内に記述を見つけることができませんでした。ので、できる範囲で調査をしてみました。

まず、本稿で実行したシーケンス操作における、`log_cnt` の推移を見てみると、下記のような更新が行われていました。

- シーケンス作成直後はlog_cntには0が設定されている
- 1回目のnextvalを実行されるとlog_cntには32が設定される
- 2回目以降のnextvalが実行されると、log_cntは1ずつ減少していく
- setvalが実行されると、log_cntは0にリセットされる

また、nextvalを繰り返し実行してlog_cntが0になった後、次のnextvalを実行すると、log_cntが32に戻ること確認しました。
その上で、[stackoverflowで見つけた書き込み](https://stackoverflow.com/questions/66456952/what-does-log-cnt-mean-in-the-postgres-sequence) を見つけ、「nextvalの都度WALレコードの書き込みは行わず、バッファリングしている」という理解を得ました。
PostgreSQLの内部構造や実装について知っていると、より解像度の高い理解を得ることができるのだろうと感じました。

## その他のシーケンス操作

その他のシーケンス操作についても見ていきます。

### ALTER SEQUENCE

[ALTER SEQUENCE](https://www.postgresql.jp/docs/16/sql-altersequence.html)のRESTARTパラメータを使うことで、シーケンスの現在位置を設定することができます。setvalと似た操作になりそうです。

大胆に現在位置を50まで飛ばしてみます。

```SQL
ALTER SEQUENCE mytable_id_seq RESTART WITH 50;
```

シーケンスの情報を確認すると、last_valueが50になり、is_calledがfalseになっています。このことから、is_calledの設定ができずfalseが設定される点に、setvalとの違いがあることが分かります。

```SQL
SELECT * FROM mytable_id_seq;
```

|last_value|log_cnt|is_called|
|----------|-------|---------|
|50        |0      |f        |

次にnextvalを実行した際には、last_valueが進まず50が返却されるため注意が必要です。

```SQL
SELECT nextval('mytable_id_seq');
```

|nextval|
|-------|
|50     |

### GUIでの操作

あるGUIのDBクライアントを使用し、シーケンスの操作を行ってみました。今度は、100まで飛ばしてみます。
実行前の確認モーダルから、下記のクエリが発行されることが分かりました。次にnextvalを実行した際には、last_valueが進まず100が返却されるということになります。

```SQL
ALTER SEQUENCE public.mytable_id_seq RESTART 100;
```

# おわりに

PostgreSQLのシーケンスについて、触りながら挙動を確かめてきました。本稿を通じ、シーケンス操作を行う際は、is_calledの値次第で次に採番される値が変わることによる混乱を防ぐ意味で、シーケンス操作関数を使うことが第一選択肢になるのだと思いました。setvalのis_calledのdefaultがtrueであることと、ALTER SEQUENCEのRESTARTパラメータのドキュメントの記載からもそのことが読み取れます。具体的なケースが思いつきませんが、どうしてもGUIを使いたいということであれば、ドキュメントと照らし合わせ発行されるクエリをきちんと理解しておく必要があるのだと思います。

今回は身近なシーケンスについて見てきましたが、PostgreSQLのその他の機能についても、今後ドキュメントを参照しつつ触ってみて、理解を深めていきたいと思います。

# エンジニア募集

Gakken LEAP では教育をアップデートしていきたいエンジニアを絶賛大募集しています。
ぜひお気軽にカジュアル面談へお越しください。

https://gakken-leap.co.jp/recruit/