Postgresql 数据库中 ID 的无缝自增实现

最近在使用Postgresql数据库时将ID字段设置为serial以实现自增，代码如下

create sequence userID_serial start 8866000;
create table userinfo(id serial, ...);
alter sequence userID_serial owned by userInfo.userID;

在插入未成功时会出现id断层即id不连续的情况，究其原因是因为：

为了避免从同一个序列获取数值的当前事务被阻塞，nextval 操作决不会回滚；也就是说，一旦一个数值已经被抓走，那么就认为它已经用过了， 即使调用nextval的事务后面又退出了也一样。 这就意味着退出的事务可能在序列赋予的数值中留下未使用的"空洞"。
——http://postgres.cn/docs/9.3/functions-sequence.html

遇到这种情况首先想到的是借助触发器在插入字段前将serial的值通过setval函数设为当前id字段最大值，即

--创建触发器函数
create function setSerialVal() returns trigger as $$
declare 
    maxid integer;
begin
    select max(userid) into maxid from userinfo;
    perform (select setval('userid_serial', maxid));
    new.userid = nextval('userid_serial');
    return new;
end;
$$ language plpgsql;

--创建触发器
create trigger serialCorrect before insert on Userinfo for each row
execute procedure setSerialVal();

触发器设置完了发现根本没必要用serial了，反正触发器每次插入都会执行，何必再多此一举使用serial呢，最终把触发器函数改为如下：

create function setSerialVal() returns trigger as $$
declare 
    nextid integer;
begin
    select max(userid) into nextid from userinfo;
    if nextid is null then nextid = 8866000;--初始值
    else nextid = nextid + 1;
    end if;
    new.userid = nextid;
    return new;
end;
$$ language plpgsql;

到此结束！