【PostgreSQL 12 新機能】パーティションテーブルを参照する外部キーの作成を検証してみた

こんにちは。サイオステクノロジー OSS サポート担当 Y です。

今回は PostgreSQL 12 (現時点ではまだ beta 版) で追加が予定されている機能であるパーティションテーブルを参照する外部キーの作成について検証してみました。(※以下の内容は CentOS 7.6/PostgreSQL 11.5/PostgreSQL 12beta3 にて検証しています。)

■はじめに

次の PostgreSQL のメジャーバージョンである PostgreSQL 12 では、複数の機能追加/強化が予定されています。今回は PostgreSQL 12beta3 を使って、パーティションテーブルを参照する外部キーの作成を検証してみました。

PostgreSQL 11 までは、パーティションテーブルを参照する外部キーを作成することができませんでしたが、PostgreSQL 12 からはその様な外部キーを作成することが可能になる予定となっています。

■検証 (PostgreSQL 11)

それでは、さっそく検証してみます。

まずは、PostgreSQL 11 での検証です。以下の様にパーティションテーブル “test” を作成します。

postgres=# SELECT version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

postgres=# 
postgres=# CREATE TABLE test (a int unique) PARTITION BY LIST (a);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE test_1 PARTITION OF test FOR VALUES IN (1);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE test_2 PARTITION OF test FOR VALUES IN (2);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE test_3 PARTITION OF test FOR VALUES IN (3);
CREATE TABLE
postgres=# 
postgres=# \d+ test
                                   Table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
Partition key: LIST (a)
Indexes:
    "test_a_key" UNIQUE CONSTRAINT, btree (a)
Partitions: test_1 FOR VALUES IN (1),
            test_2 FOR VALUES IN (2),
            test_3 FOR VALUES IN (3)

postgres=# 

次に、このテーブル test を参照する外部キーを持つテーブル “ref_test” を作成してみます。

postgres=# CREATE TABLE ref_test (a int REFERENCES test (a));
ERROR:  cannot reference partitioned table "test"
postgres=# 

すると、上記の様にパーティションテーブルを参照することができない旨のエラーが出力されます。これが PostgreSQL 11 までの仕様になっています。

■検証 (PostgreSQL 12beta3)

今度は、PostgreSQL 12beta3 で同様の検証を実施してみます。

PostgreSQL 12beta3 で、先程と同じ様にパーティションテーブル “test” を作成します。

postgres=# SELECT version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 12beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

postgres=# 
postgres=# CREATE TABLE test (a int unique) PARTITION BY LIST (a);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE test_1 PARTITION OF test FOR VALUES IN (1);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE test_2 PARTITION OF test FOR VALUES IN (2);
CREATE TABLE
postgres=# 
postgres=# CREATE TABLE test_3 PARTITION OF test FOR VALUES IN (3);
CREATE TABLE
postgres=# 
postgres=# \d+ test
                             Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
Partition key: LIST (a)
Indexes:
    "test_a_key" UNIQUE CONSTRAINT, btree (a)
Partitions: test_1 FOR VALUES IN (1),
            test_2 FOR VALUES IN (2),
            test_3 FOR VALUES IN (3)

postgres=# 

次に、このテーブル test を参照する外部キーを持つテーブル “ref_test” を作成してみます。

postgres=# CREATE TABLE ref_test (a int REFERENCES test (a));
CREATE TABLE
postgres=# 
postgres=# \d+ test
                             Partitioned table "public.test"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
Partition key: LIST (a)
Indexes:
    "test_a_key" UNIQUE CONSTRAINT, btree (a)
Referenced by:
    TABLE "ref_test" CONSTRAINT "ref_test_a_fkey" FOREIGN KEY (a) REFERENCES test(a)
Partitions: test_1 FOR VALUES IN (1),
            test_2 FOR VALUES IN (2),
            test_3 FOR VALUES IN (3)

postgres=# 
postgres=# \d+ ref_test
                                 Table "public.ref_test"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
Foreign-key constraints:
    "ref_test_a_fkey" FOREIGN KEY (a) REFERENCES test(a)
Access method: heap

postgres=# 

すると、上記の様にパーティションテーブルを参照する外部キーを持つテーブル “ref_test” を作成することができました。

最後に、実際に外部キーによる制約が動作するか否かを検証してみます。

以下の様に被参照テーブルである test に 1, 2, 3 を INSERT します。

postgres=# INSERT INTO test VALUES (1), (2), (3);
INSERT 0 3
postgres=# 
postgres=# SELECT * FROM test;
 a 
---
 1
 2
 3
(3 rows)

postgres=# 

この状態で ref_test に 1, 2, 3, 4 を INSERT すると、1, 2, 3 は正常に INSERT できますが、test.a に値が存在していない “4” を INSERT しようとすると、外部キー制約違反を示すエラーが出力され INSERT できませんでした。

postgres=# INSERT INTO ref_test VALUES (1);
INSERT 0 1
postgres=# 
postgres=# SELECT * FROM ref_test;
 a 
---
 1
(1 row)

postgres=# 
postgres=# INSERT INTO ref_test VALUES (2);
INSERT 0 1
postgres=# 
postgres=# SELECT * FROM ref_test;
 a 
---
 1
 2
(2 rows)

postgres=# 
postgres=# INSERT INTO ref_test VALUES (3);
INSERT 0 1
postgres=# 
postgres=# SELECT * FROM ref_test;
 a 
---
 1
 2
 3
(3 rows)

postgres=# 
postgres=# INSERT INTO ref_test VALUES (4);
ERROR:  insert or update on table "ref_test" violates foreign key constraint "ref_test_a_fkey"
DETAIL:  Key (a)=(4) is not present in table "test".
postgres=# 

上記の通り、パーティションテーブルを参照する外部キーの作成及び動作を確認することができました。

■最後に

今回は PostgreSQL 12beta3 で、パーティションテーブルを参照する外部キーの作成を検証してみました。

PostgreSQL 10 で導入された宣言的パーティショニングに関する機能も、バージョンを重ねるごとに少しずつ強化されており、少しずつ恩恵を受けることができる範囲が広がってきているようです。

今後も、機会があれば PostgreSQL 12 や既存のバージョンにおける機能の検証を実施してみようと思います。

About サイオステクノロジーの中の人 41 Articles

サイオステクノロジーで働く中の人です。

Twitter YouTube