PostgreSQL

PostgreSQL 自動採番カラム(GENERATED ALWAYS AS IDENTITYに寄せてみる)

PostgreSQL版 generated always as identityをなんとかやってみる

以前こちらの記事でPostgreSQLで自動採番を実現する例を紹介しましたが、この時自動採番対象カラムに対し、PKもしくはユニークキーを指定した場合「自動採番カラムに外から値を設定すると重複エラー」になる可能性があるので、何かしら対策が必要だという事に気づきました。
Oracleであれば自動採番対象カラムをgenerated always as identity属性にしてしまえばこの問題も解決できるのですが、PostgreSQLではこれに相当する機能が見当たらないので今回なんとか自作(トリガーで実装)してみたので以下紹介します。

検証環境を以下の通り作成します
create table  logging (
        logid           integer
    ,   trace_no        integer
    ,   step_code       varchar(8)
    ,   step_message    varchar(128)
    ,   creation_date   timestamp
);
-- pk の作成
alter table logging add constraint pk_logging primary key (logid)
;
-- pk用のシーケンス
create sequence logging_logid_seq
;
トリガー用ファンクションの作成

logidカラムについては、外部からinsertで指定してほしくないため「if new.logid is not null then」でチェックしています。未設定の場合のみシーケンス値をlogidカラムにセットしています。

CREATE or replace FUNCTION set_logging_logid()
RETURNS TRIGGER AS $$
BEGIN
   if new.logid is not null then
       RAISE EXCEPTION 'Direct insertion of logid is not allowed';
   end if;
   new.logid := nextval('logging_logid_seq');
   return NEW;
END;
$$ LANGUAGE plpgsql;
上記ファンクションをloggingテーブルのトリガーとして登録
create or replace TRIGGER set_logging_logid_trigger
BEFORE INSERT ON logging
FOR EACH ROW
EXECUTE FUNCTION set_logging_logid();
動作検証
-- 自動採番カラムに値を設定しないとINSERTがされて、自動採番カラム
-- にもシーケンス値が設定される
--
udondb=# insert into logging(trace_no,step_code,step_message,creation_date)
udondb-# values (10,'STEP01','処理開始',now());
INSERT 0 1
udondb=#
udondb=# select * from logging;
 logid | trace_no | step_code | step_message |       creation_date
-------+----------+-----------+--------------+----------------------------
     1 |       10 | STEP01    | 処理開始     | 2023-07-09 14:47:55.668041
(1 行)


udondb=#
--
-- 自動採番カラムに対し外部から値を指定してみる
-- トリガーで実装した通りエラーとなり、自動採番カラム(logid)に対し
-- 外から値を指定する事ができなくなっている。
-- 
udondb=# insert into logging(logid ,trace_no,step_code,step_message,creation_date)
udondb-# values (3,10,'STEP01','処理開始',now());
ERROR:  Direct insertion of logid is not allowed
CONTEXT:  PL/pgSQL function set_logging_logid() line 4 at RAISE
udondb=#
--
-- トリガーを一時的に無効化する
-- 
udondb=# alter table logging disable trigger set_logging_logid_trigger
udondb-# ;
ALTER TABLE
--
-- トリガーが動作しないので今度はINSERTできる。
-- (PKルールをパスできるように事前に調査済)
-- 
udondb=# insert into logging(logid ,trace_no,step_code,step_message,creation_date)
udondb-# values (3,10,'STEP01','処理開始',now());
INSERT 0 1
udondb=# select * from logging;
 logid | trace_no | step_code | step_message |       creation_date
-------+----------+-----------+--------------+----------------------------
     1 |       10 | STEP01    | 処理開始     | 2023-07-09 14:47:55.668041
     3 |       10 | STEP01    | 処理開始     | 2023-07-09 14:54:29.684247
(2 行)

-- シーケンス値を確認すると、「1」のままである。このままでは
-- 先ほど手動で入れた「3」と重複する可能性があるのでシーケンス値を
-- 手動で進める必要がある。
--
udondb=# select last_value from logging_logid_seq;
 last_value
------------
          1
(1 行)


-- 
-- 以下の通りシーケンス値を3に変更する
--
udondb=# select setval('logging_logid_seq',3);
 setval
--------
      3
(1 行)

-- 一時的に無効にしていたトリガーを有効に戻す
--

udondb=# alter table logging enable trigger set_logging_logid_trigger;
ALTER TABLE
udondb=#
--
-- 再度自動採番カラムを指定しないでinsert文を発行する
-- 当然エラーなく終了する
--
udondb=# insert into logging(trace_no,step_code,step_message,creation_date)
udondb-# values (12,'STEP02','処理終了',now());
INSERT 0 1

--
-- insertされた値を確認すると「4」が設定されている(想定通り)
--
udondb=# select * from logging;
 logid | trace_no | step_code | step_message |       creation_date
-------+----------+-----------+--------------+----------------------------
     1 |       10 | STEP01    | 処理開始     | 2023-07-09 14:47:55.668041
     3 |       10 | STEP01    | 処理開始     | 2023-07-09 14:54:29.684247
     4 |       12 | STEP02    | 処理終了     | 2023-07-09 15:05:41.052366
(3 行)


udondb=#

Oracleと異なり、Naitiveの機能ではないので、「logidカラムが自動採番カラムでかつ外部からの指定ができない事」はPostgreSQLのどの設定ファイルや管理テーブルを見てもわかりません。開発者がこれら認識する必要がありますが、これでなんとか使えるのではないかと思います。

スポンサーリンク