本記事は PostgreSQL Advent Calendar 2024 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 ユーザーは参照できません。
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> SELECT seqence_name.NEXTVAL;
NEXTVAL
----------
1
SQL> SELECT sequence_name.CURRVAL;
CURRVAL
----------
1
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）に保存されます。
Oracle Database では共有メモリー領域（SGA / PostgreSQL の shared_buffers）にキャッシュを保持します。このため時系列とシーケンス値が前後することはありません。ただし Real Application Clusters (RAC) の場合は例外です。
上記の仕様から複数のセッションから同一のシーケンスを呼び出す場合、Oracle Database と PostgreSQL では時系列とシーケンス値の帰り方が異なります。
参考
Oracle Database 23ai SQL Reference: CREATE SEQUENCE
PostgreSQL 17 Documentation: CREATE SEQUENCE