PostgreSQLにおいて、スキーマ作成時にバリデーションを走らせる

業務で必要になったので実装した。

## やりたいこと

- PostgreSQLにおいて、あるデータベース内に作られるスキーマの数を５以下に制限したい。
    - (`information_schema`, `pg_*`,`public`を除く)
- ~~複数のクライアントから同時にスキーマが作成される可能性があるので、競合状態になるのを防ぎたい~~
    - [追記]本ページの実装では競合状態を防げていなかった。テーブルロックが必要だった。
- (既存実装の都合上) SQLのクエリ内で全てを完結させたい


## 実装

スキーマの新規作成時に、以下のクエリを実行する。

```sql
CREATE OR REPLACE PROCEDURE validate_number_of_schemas() AS $$ 
DECLARE
  schema_cnt int;
BEGIN
  SELECT COUNT(nspname)  
  INTO schema_cnt                           -- schema_cnt に代入。
  FROM pg_catalog.pg_namespace;
  WHERE nspname NOT LIKE 'pg_%' AND nspname NOT IN ('information_schema', 'public')

  IF schema_cnt > 3 THEN
    RAISE EXCEPTION 'Schema cannot be created any more!';
  END IF;
END;
$$ LANGUAGE plpgsql; 

BEGIN;
CREATE SCHEMA new_schema;
CALL validate_number_of_schemas();
COMMIT;
```

ref. [PL/pgSQLを使い、PostgreSQLのクエリ内で条件分岐して例外発生を起こす
](https://qiita.com/kudojp/items/3115467ba6a61b0fe1f5)


## 実機検証

### 下準備

動作確認用のデータベースを作成する。

```bash
$ createdb test_db
$ psql -d test_d
```

スキーマを３つ(上限まで)作る。

```
test_db=# CREATE SCHEMA first_schema;
CREATE SCHEMA
test_db=# CREATE SCHEMA second_schema;
CREATE SCHEMA
test_db=# CREATE SCHEMA third_schema;
CREATE SCHEMA
```
スキーマが３つできていることを確認する。

```
test_db=# \dn
      List of schemas
     Name      |   Owner    
---------------+------------
 first_schema  | daiki-kudo
 public        | daiki-kudo
 second_schema | daiki-kudo
 third_schema  | daiki-kudo
(4 rows)
```

<details>
<summary>スキーマ一覧は pg_catalog.pg_namespace テーブルに収納されている</summary>

```
test_db=# SELECT * FROM pg_catalog.pg_namespace;
  oid   |      nspname       | nspowner |                          nspacl                           
--------+--------------------+----------+-----------------------------------------------------------
     99 | pg_toast           |       10 | 
     11 | pg_catalog         |       10 | {"\"daiki-kudo\"=UC/\"daiki-kudo\"","=U/\"daiki-kudo\""}
   2200 | public             |       10 | {"\"daiki-kudo\"=UC/\"daiki-kudo\"","=UC/\"daiki-kudo\""}
  13657 | information_schema |       10 | {"\"daiki-kudo\"=UC/\"daiki-kudo\"","=U/\"daiki-kudo\""}
 180900 | first_schema       |       10 | 
(5 rows)
```

従って以下でもスキーマ一覧が確認できる。

```
test_db=# SELECT COUNT(*) FROM pg_catalog.pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname NOT IN ('information_schema', 'public');
 count 
-------
     5
(1 row)

```

</details>

### バリデーションが効くことの確認

以下を実行して、４つ目のスキーマの作成を試みる。
(---以下は実際に実行した時の出力)

```sql
CREATE OR REPLACE PROCEDURE validate_number_of_schemas() AS $$ 
DECLARE
  schema_cnt int;
BEGIN
  SELECT COUNT(nspname)  
  INTO schema_cnt                           -- schema_cnt に代入。
  FROM pg_catalog.pg_namespace;
  WHERE nspname NOT LIKE 'pg_%' AND nspname NOT IN ('information_schema', 'public')

  IF schema_cnt > 3 THEN
    RAISE EXCEPTION 'Schema cannot be created any more!';
  END IF;
END;
$$ LANGUAGE plpgsql; 
--- CREATE PROCEDURE

BEGIN;
CREATE SCHEMA fourth_schema;  ----------------- 🌟
--- CREATE SCHEMA
CALL validate_number_of_schemas();
--- ERROR:  Schema cannot be created any more!
--- CONTEXT:  PL/pgSQL function validate_number_of_schemas() line 7 at RAISE
COMMIT;
--- ROLLBACK
```

### トランザクション中に 他のセッションにおいて、作成中のスキーマのdirty read が発生しないことの確認

前章の🌟の時点で別のシェルで `$ psql -d test_db`を開き、fourth_schemaがスキーマのリストに含まれていない(つまり、dirty readが発生しない)ことを確認する。

```
test_db=# \dn
      List of schemas
     Name      |   Owner    
---------------+------------
 first_schema  | daiki-kudo
 public        | daiki-kudo
 second_schema | daiki-kudo
 third_schema  | daiki-kudo
(4 rows)
```


## まとめ

"create schema" と "validation" を同一トランザクション内に入れてやる、というのが肝。

```sql
BEGIN;
CREATE SCHEMA fourth_schema;
CALL validate_number_of_schemas();
COMMIT;
```