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

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

やりたいこと

• PostgreSQLにおいて、あるデータベース内に作られるスキーマの数を５以下に制限したい。
  • (information_schema, pg_*,publicを除く)
• 複数のクライアントから同時にスキーマが作成される可能性があるので、競合状態になるのを防ぎたい
  • [追記]本ページの実装では競合状態を防げていなかった。テーブルロックが必要だった。
• (既存実装の都合上) 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のクエリ内で条件分岐して例外発生を起こす

実機検証

下準備

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

$ 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)

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

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)

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

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

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" を同一トランザクション内に入れてやる、というのが肝。

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