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のどの設定ファイルや管理テーブルを見てもわかりません。開発者がこれら認識する必要がありますが、これでなんとか使えるのではないかと思います。
