PostgreSQL 18がやってくる(8) SQL:2011 application time

# はじめに
にゃーん。
今日はPostgreSQL 18で追加された「application time」について調べてみました。

# What's application time?
この機能自体、全然知らなかったけど、SQL規格（[SQL:2011](https://en.wikipedia.org/wiki/SQL:2011)で追加された仕様（"Time period definitions"）のことらしい。

**非常にざっくり言うと、時刻によって異なる値を管理する機能のようだ。**
例えば業務内容は変わらないけど、年度によって部署名が変わる（弊社でもそうだけど、組織や組織名が数年毎に変わるのよね・・・）、というようなケースで使えるようだ。

PostgreSQL文書（開発版）の[D.2. Unsupported Features](https://www.postgresql.org/docs/devel/unsupported-features-sql-standard.html)の項番T181 "Application-time period tables"に該当するものだと思う。現時点[^1]でUnsupported Featuesとされている理由は不明（まだSQL規格としての実装が不十分なのか、単に文書の修正漏れなのかは不明）。
本体機能として取り込まれたのはPostgreSQL 18からだが、それ以前の版では[temporal_tables](https://github.com/arkhipov/temporal_tables)という拡張機能で似たようなこと（但しSQL:2011準拠の設計ではなさそう）ができるらしい。

# 概要
[Commitfest 2025-03](https://commitfest.postgresql.org/52/)でコミットされた項目である。

|タイトル|[SQL:2011 application time](https://commitfest.postgresql.org/patch/4308/)|
|:--|:--|
|Topic|SQL Commands|
|Last modified|2025-03-18 09:47:04|
|Emails|[SQL:2011 application time](https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com)|

この項目も例によって、MLでの議論が長大なのだけど、ほとんど内容は追えてない・・・。

# SQL文の修正
PostgreSQL文書（開発版）の``CREATE TABLE``に以下の修正が入っている。

PostgreSQL 17とPostgreSQL 18のSQL構文(Synopsis)を比較し、この機能に関するSQL文構文の差分は以下のように見える。

```sql
57,58c57,59
<   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
<   PRIMARY KEY ( column_name [, ... ] ) index_parameters |
---
>   NOT NULL column_name [ NO INHERIT ] |
>   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
>   PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
60c61
<   FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
---
>   FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD refcolumn ] ) ]

```
おそらく、
* UNIQUE制約
* PRIMARY KEY制約
* FOREIGN KEY制約

に``WITHOUT OVERLAPS``キーワード（期間重複しない、という意味かな）付きのカラムが指定可能になった、という修正のようだ。（きちんと調べてないけど、``ALTER TABLE``も同様の修正が入っているはず）。
そして、``PERIOD column_name``に指定する型として範囲型（[Range Type](https://www.postgresql.org/docs/devel/rangetypes.html)）を指定する。

:::note info
"SQL:2011 application time"では期間（日付やタイムスタンプ）を使うことを前提としているように思えるが、PostgreSQLの今回の実装では、特に日付やタイムスタンプに限らず、任意の範囲型を使用することができる。
> (This is sometimes called a temporal key, if the column is a range of dates or timestamps, but PostgreSQL allows ranges over any base type.) 
:::


# とりあえず使ってみる
あるテーブルの列が時刻によって刻々と変わる、という例を試してみる。

以下のようなテーブルを定義する。
```sql
CREATE TABLE foo (
  id int,
  name text,
  valid_period daterange,
  -- PK: id(btree_gist) + valid_period)
  CONSTRAINT foo_pk PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
);
```
* テーブル``foo``は``id``, ``name``, ``valid_period``という列を持つ。
* ``valid_period``は日付範囲型を使う。
* PRIMARY KEYとして、``id``と``valid_period WITHOUT OVERLAPS``を指定する（ここがPostgreSQL 18での修正ポイント）。

## はまりポイントと回避方法
上記のDDLはそのまま実行しようとすると
```sql
$ psql -p 18001 -U postgres testdb -a -f 0_simple_table.sql
-- application time test
DROP TABLE IF EXISTS foo ;
DROP TABLE
CREATE TABLE foo (
  id int,
  name text,
  valid_period daterange,
  -- PK: id(btree_gist) + valid_period)
  CONSTRAINT foo_pk PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
);
psql:0_simple_table.sql:9: ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
```
``ERROR:  data type integer has no default operator class for access method "gist"``というエラーになる。
最初、なぜ突然にGiSTインデックスメソッドが？と思ったが、どうやら
「[btree_gist](https://www.postgresql.org/docs/devel/btree-gist.html)を使え、と~~ガンダム~~PostgreSQL文書が言っている」
ようだ。
PostgreSQL 18文書（開発版）の[CREATE TABLE](https://www.postgresql.org/docs/devel/sql-createtable.html)のページにこんな記述がある。
> The WITHOUT OVERLAPS column must have a range or multirange type. Empty ranges/multiranges are not permitted. The non-WITHOUT OVERLAPS columns of the constraint can be any type that can be compared for equality in a GiST index. By default, only range types are supported, but you can use other types by adding the btree_gist extension (which is the expected way to use this feature).

* WITHOUT OVERLAPS以外の列は、GiSTインデックスで等しいかどうかを比較できる任意の型を指定する。
* でも、通常の基本型(integerやtext等)はGiSTインデックス対応の演算子を持ってない。
* （基本型を含める場合には）btree_gist拡張を追加して使うことができる。

ということらしい。

:::note info
btree_gist拡張はcontribモジュールに含まれている。
また、主なPaaS（AWS Aurora, Azure Database for PostgreSQL, AlloyDB, OCI PostgreSQL）でも使用可能になっている。
:::

ということで、btree_gist拡張機能を使用可能[^2]にし、``CREATE EXTENSION``コマンドで``btree_gist``をデータベースに登録する。
登録後は例えばint型でも、GiST対応の演算子が使えるようになるので、元々のDDLも正常に実行できるようになる。

```sql
$ psql -p 18001 -U postgres testdb -a -f 1_simple_table.sql
-- application time test
CREATE EXTENSION btree_gist;
CREATE EXTENSION
DROP TABLE IF EXISTS foo ;
psql:1_simple_table.sql:4: NOTICE:  table "foo" does not exist, skipping
DROP TABLE
CREATE TABLE foo (
  id int,
  name text,
  valid_period daterange,
  -- PK: id(btree_gist) + valid_period)
  CONSTRAINT foo_pk PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
);
CREATE TABLE
```
作成したテーブルの定義を``\d+``メタコマンドで確認してみる。
```sql
testdb=# \d+ foo
                                               Table "public.foo"
    Column    |   Type    | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------------+-----------+-----------+----------+---------+----------+-------------+--------------+-------------
 id           | integer   |           | not null |         | plain    |             |              |
 name         | text      |           |          |         | extended |             |              |
 valid_period | daterange |           | not null |         | extended |             |              |
Indexes:
    "foo_pk" PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
Not-null constraints:
    "foo_id_not_null" NOT NULL "id"
    "foo_valid_period_not_null" NOT NULL "valid_period"
Access method: heap
```
明示的に定義したPRIMARY KEY(``foo_pk``)以外に、NOT NULL 制約が2つ追加されている。これはPRIMARY KEYが、NOT NULL制約かつUNIQUE制約なので自動的にNOT NULL制約も、PRIMARY KEYを構成する列（``id``, ``valid_period``）につくことになる。

## データ挿入
テーブル定義が無事に終わったので、データを挿入する。

```sql
$ psql -p 18001 -U postgres testdb -a -f 1_insert_table.sql
-- application time test
INSERT INTO foo VALUES
(1, 'HQ', '[2020-01-01,9999-12-31]'),
(2, 'Dev1-Team1', '[2020-04-01,2022-03-31]'),
(2, 'Dev1-Team2', '[2022-04-01,2024-03-31]'),
(2, 'Dev2-TeamX', '[2024-04-01,2025-03-31]'),
(3, 'Human Recource', '[2020-01-01,9999-12-31]')
;
INSERT 0 5
```
今回は5つのレコードを挿入する。``id=2``のレコードを3つ挿入している。この3レコードの``valid_period``列は日付範囲型であるが、これが重ならない（OVERLAPしない）ようなデータにしている。（重なりがあると以下のような制約違反エラーになる）
```sql
$ psql -p 18001 -U postgres testdb -a -f 1_error_insert.sql
-- application time test
INSERT INTO foo VALUES
(1, 'HQ', '[2020-01-01,9999-12-31]'),
(2, 'Dev1-Team1', '[2020-04-01,2022-03-31]'),
(2, 'Dev1-Team2', '[2022-03-31,2024-03-31]')
;
psql:1_error_insert.sql:6: ERROR:  conflicting key value violates exclusion constraint "foo_pk"
DETAIL:  Key (id, valid_period)=(2, [2022-03-31,2024-04-01)) conflicts with existing key (id, valid_period)=(2, [2020-04-01,2022-04-01)).
```

## 検索
5行のレコードを挿入した後、``foo``テーブルの内容を見てみる。
```sql
testdb=# TABLE foo ;
 id |      name      |       valid_period
----+----------------+--------------------------
  1 | HQ             | [2020-01-01,10000-01-01)
  2 | Dev1-Team1     | [2020-04-01,2022-04-01)
  2 | Dev1-Team2     | [2022-04-01,2024-04-01)
  2 | Dev2-TeamX     | [2024-04-01,2025-04-01)
  3 | Human Recource | [2020-01-01,10000-01-01)
(5 rows)
```
"application time"っぽい検索をするために、条件として「ある日」時点の部署名（``name``）を検索してみる。
このときに、``valid_period``列に条件を指定するが、範囲型なのでフツーの``=``演算子とかは使えない。
```sql
testdb=# SELECT * FROM foo WHERE id = 2 AND valid_period = '2022-01-01';
ERROR:  malformed range literal: "2022-01-01"
LINE 1: SELECT * FROM foo WHERE id = 2 AND valid_period = '2022-01-0...
                                                          ^
DETAIL:  Missing left parenthesis or bracket.
```
たとえば、'2022-01-01'時点の部署名を検索したい場合には、``範囲型 @> 日付``のように``@>``を使う必要がある（正確には ``@>``演算子は包含、つまりを'2022-01-01'を含む範囲を示す）。

```sql
testdb=# SELECT * FROM foo WHERE valid_period @> '2022-01-01'::date;
 id |      name      |       valid_period
----+----------------+--------------------------
  1 | HQ             | [2020-01-01,10000-01-01)
  2 | Dev1-Team1     | [2020-04-01,2022-04-01)
  3 | Human Recource | [2020-01-01,10000-01-01)
(3 rows)

testdb=# SELECT * FROM foo WHERE valid_period @> '2023-01-01'::date;
 id |      name      |       valid_period
----+----------------+--------------------------
  1 | HQ             | [2020-01-01,10000-01-01)
  2 | Dev1-Team2     | [2022-04-01,2024-04-01)
  3 | Human Recource | [2020-01-01,10000-01-01)
(3 rows)

testdb=# SELECT * FROM foo WHERE valid_period @> '2025-01-01'::date;
 id |      name      |       valid_period
----+----------------+--------------------------
  1 | HQ             | [2020-01-01,10000-01-01)
  2 | Dev2-TeamX     | [2024-04-01,2025-04-01)
  3 | Human Recource | [2020-01-01,10000-01-01)
(3 rows)
```
``id``と``valid_period``の条件によって、application time相当の検索を実現している、ということのようだ。

なお、PostgreSQLのapplication nameの（現状の）実装では、範囲型を使っているので、範囲型の演算子は全て使える。
例えば、「'2022-03-01'から'2022-04-30'までの部署名を検索したい」という検索の場合、``範囲型 && 範囲型``のような演算子を使うこともできる。
```sql
testdb=# SELECT * FROM foo WHERE id = 2 AND valid_period && '[2022-03-01,2022-04-30]';
 id |    name    |      valid_period
----+------------+-------------------------
  2 | Dev1-Team1 | [2020-04-01,2022-04-01)
  2 | Dev1-Team2 | [2022-04-01,2024-04-01)
(2 rows)
```

# おわりに
今回は目玉機能の一つになるかもしれない（個人の感想です）、"SQL:2011 application time"について調べてみました。
PostgreSQL 18での実装は、制約にOVERLAPSの指定を追加可能にしたというものなので、SQL:2011の規格に完全に準拠したものかは、正直自分にはよくわからないとこもありますが、なかなか面白い機能だと思います。

ただ、基本型と組み合わせる場合、btree_gist拡張機能を追加しないといけないのと、範囲型演算子を使わないといけないのが、ちょい面倒な印象・・・。


[^1]: 2025-05-03現在。
[^2]:ソースビルドの場合、contrib/btree_gist ディレクトリ配下でmake, make installする。RPMパッケージなら\*contrib\*.rpmに入っているはず。PaaS上の場合、それぞれの環境で拡張機能を有効化する設定があるので、それに従う。