[pgsql-jp: 40588] Re: グループ毎に自動採番したいのですが

[Itagaki Takahiro]

2010/12/4 yum <yum ＠ ishina.net>:
> その2. ID管理テーブルを作る
> CREATE TABLE grp_s_id_tbl (
> group_id int not null PRIMARY KEY,
> max_s_id int not null default 0
> );

これと似た形で、毎回 max() を計算しても良いかもしれません。
最初の行は特別な処理が要りますが、アプリ的にも特別扱いが
必要な気がするので、そちらでカバーすることになるのでは。

一般的には、max()によるID生成はテーブルロックが必要になるので
推奨されませんが、今回の場合は代わりに行ロックで済むので
u_id がバラけている限りは大丈夫だと思います。

[その3 : トリガでmax()]
CREATE FUNCTION assign_s_id() RETURNS TRIGGER AS $$
BEGIN
  PERFORM 1 FROM users WHERE u_id = 1 AND s_id = 1 FOR UPDATE;
-- IF NOT FOUND
--   FIXME: u_idごとの最初の行の時には別のロックが要る？
-- END IF;
  SELECT coalesce(max(s_id), 0) + 1 INTO NEW.s_id FROM users WHERE
u_id = NEW.u_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_insert BEFORE INSERT ON users FOR EACH ROW
  EXECUTE PROCEDURE assign_s_id();

INSERT INTO users (u_id, s_id) VALUES (<値>, DEFAULT);

-- 
Itagaki Takahiro