PostgreSQL と Oracle Database でシーケンスの動作を比較する

本記事は [PostgreSQL Advent Calendar 2024](https://qiita.com/advent-calendar/2024/postgresql) 20 日目の記事です。PostgreSQL と Oracle Database でシーケンス・オブジェクトの違いを検証します。Oracle Database 互換を目指す IvorySQL の情報も追加しています。データベースのバージョンは Oracle Database 23ai Free, PostgreSQL 17.2, IvorySQL 17devel を使用します。


昨日は @ShinyaKato_ さんの「拡張機能をRustで作ってみた話」でした。明日は　@discus_hamburg さんの「標準はあるにはあるが癖の多いSQLシリーズ」の予定です。

# シーケンスの作成
　シーケンスの作成は Oracle Database、PostgreSQL 共に CREATE SEQUENCE 文を実行します。CREATE SEQUENCE 文は IvorySQL では変更しておらず、PostgreSQL と違いは無いようです。シーケンスを作成する CREATE SEQUENCE 文については以下の違いがあります。

|Oracle Database| PostgreSQL|相違点|
|:---      |:---         |:---     |
|SESSION   | TEMPORARY	|構文の違い|
|GLOBAL	   |-            | PostgreSQL では構文無し |
|-         |UNLOGGED     | Oracle では構文無し |
|-         |OWNED BY     | Oracle では構文無し |
|[user.]sequence_name|[schema.]sequence_name|ユーザーとスキーマの違い|
|IF NOT EXISTS|IF NOT EXISTS||
|-         |AS type|Oracle では構文無し|
|INCREMENT BY|INCREMENT BY|      |
|START WITH n|START WITH n|          |
|MAXVALUE n|MAXVALUE n|          |
|NOMAXVALUE|NO MAXVALUE|構文の違い|
|MINVALUE n|MINVALUE n|          |
|NOMINVALUE|NO MINVALUE|構文の違い|
|CYCLE     |CYCLE     |          |
|NOCYCLE   |NO CYCLE  |構文の違い |
|CACHE n   |CACHE n   |デフォルトキャッシュ数|
|NOCACHE   |-         |PostgreSQL では構文無|
|[NO]ORDER |-         |PostgreSQL では構文無|
|[NO]KEEP  |-         |PostgreSQL では構文無|
|[NO]SCALE [NO]EXTEND|- |PostgreSQL では構文無|
|SHARD [NO]EXTEND|-    |PostgreSQL では構文無|

## 実行権限
Oracle Database はシーケンスの作成に CREATE SEQUENCE システム権限が必要ですが PostgreSQL (IvorySQL) ではシーケンス作成スキーマに対する CREATE 権限が必要です。

## データ型の違い
PostgreSQL では AS 句で指定できるデータ型は SMALLINT / INT / BIGINT です。

## 構文の違い
CYCLE 句 MAXVALUE 句等の値を指定しない場合は先頭に「NO」を付けます。PostgreSQL では「NO」の後ろにスペースが必要ですが、Oracle Database では必要ありません。

## キャッシュ数の違い
PostgreSQL ではデフォルトのキャッシュ数は 1 です。Oracle Database のデフォルト値は 20 で、最小値は 2 になります。

## メタデータの取得
PostgreSQL ではカタログ pg_sequences を参照します。Oracle Database では USER|ALL|DBA_SEQUENCES ビューを参照します。IvorySQL では sys.dba_sequences / sys.user_sequences / sys.all_sequences ビューが提供されています。ただし標準では非 SUPERUSER ユーザーは参照できません。

```SQL: IvorySQL のシーケンス・メタデータ参照
postgres=# SELECT * FROM sys.dba_sequences;
-[ RECORD 1 ]--+---------------------------
sequence_owner | postgres
sequence_name  | userenv_sessionid_sequence
min_value      | 1
max_value      | 9223372036854775807
increment_by   | 1
cycle_flag     | f
order_flag     |
cache_size     | 20
last_number    |
scale_flag     | N
extend_flag    | N
shared_flag    |
session_flag   | N
keep_value     |
```

# シーケンスの呼び出し
　Oracle Database と PostgreSQL ではシーケンス値の呼び出し方法は異なります。IvorySQL では Oracle Database と PostgreSQL の構文両方が利用できます。NEXTVAL を呼び出した後でないと、CURRVAL の呼び出しができない点はどのデータベースでも同じです。
 
 ```SQL: Oracle Database / IvorySQL のシーケンス値取得
SQL> SELECT seqence_name.NEXTVAL;
   NEXTVAL
----------
         1 

SQL> SELECT sequence_name.CURRVAL;
   CURRVAL
----------
         1
 ```
 
```SQL:PostgreSQLのシーケンス値取得
postgres=> SELECT NEXTVAL('sequence_name');
-[ RECORD 1 ]
nextval | 2

postgres=> SELECT CURRVAL('sequence_name');
-[ RECORD 1 ]
currval | 2
```


# シーケンス値と時系列
PostgreSQL と Oracle Database はどちらもシーケンス値のキャッシュ機能を持っています。しかしキャッシュの保存メモリー領域は両者で異なります。PostgreSQL / IvorySQL ではバックエンド・プロセス（Oracle Database でいう PGA）に保存されます。

![PostgreSQLシーケンスの動作](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/181284/15e355c2-d1c8-2eac-9aa8-4e9f33a459da.png)

Oracle Database では共有メモリー領域（SGA / PostgreSQL の shared_buffers）にキャッシュを保持します。このため時系列とシーケンス値が前後することはありません。ただし Real Application Clusters (RAC) の場合は例外です。

![Oracle Database シーケンスの動作](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/181284/a6617f50-32fd-a523-492c-8f2b8ff82b8e.png)


上記の仕様から複数のセッションから同一のシーケンスを呼び出す場合、Oracle Database と PostgreSQL では時系列とシーケンス値の帰り方が異なります。

# 参考
Oracle Database 23ai SQL Reference: [CREATE SEQUENCE](https://docs.oracle.com/cd/F82042_01/sqlrf/CREATE-SEQUENCE.html)
PostgreSQL 17 Documentation: [CREATE SEQUENCE](https://www.postgresql.org/docs/current/sql-createsequence.html)

Author: Noriyoshi Shinoda / Date: December 20, 2024