postgresql迁移sql server中的sequence之坑

序列重置（问题与解决方案）

为了避免脏数据的影响，VSM每次重启都会对数据表中的序列进行重置。
pg中的代码实现为：

select nextVal('%s_id_seq'),setVal('%s_id_seq', %d)

移植为sql server后的代码为：

alter sequence %s_id_seq restart with %d

特别要注意的是：如果sqlserver 重置时restart with 同样使用
setVal的值的话，每次重启VSM后初次添加资源都会失败，原因在于setVal使用的是从表中查到的最大值，pg中再次向序列查询时会从最大值+1开始，而(sqlserver的)restart
with也使用最大值时，再次向序列查询时会从最大值开始。所以在调用上层接口时应将起始值+1后再传入，如果对代码不熟悉这个缺陷很难被发现和修复

问题详述

一般来说数据表中都会有一个ID字段，在向数据库中插入数据的时候往往也希望ID能够连续的自增，为了达到这个目的其中一个方法就是，每次插入数据时都去查下现在数据库中的最大ID是多少，然后将ID加1就能得到下一个ID的值。

原项目中采用的是PG数据库库，这个需求的实现是借助sequence特性来完成，每次都去通过这序列找到下一个应该插入的ID值。sequence这个特性在sql server中也是支持的，所以在进行数据库迁移时也采用了这种方法，对等的移植了相关sql语句。但是最后迁移完成进行测试时却发现，每次重启服务，然后去向数据库中插入数据时，第一次总是失败，失败的原因很明显，就是第一次插入时的ID不是原有ID最大值的下一个ID，而是最大的ID。很显然是服务器逻辑在重启时，为了避免ID不连续，重置了序列的ID值。但具体问题出在哪里呢？ sql server与postgresql之间同一特性之间的对等移植，究竟有什么差异呢？

通过下面的例子一探究竟：

准备数据

create table test1
(
id int,
name varchar(32)
);
create sequence test1_id_seq increment by 1 minvalue 1 no maxvalue start with 1;   
insert into test1(id,name)values(1,'hhh');
insert into test1(id,name)values(2,'hhh');

查询结果：

SELECT * from test1;

可以看到其中有两条数据

查询最大值：

select max(id) from test1

模拟pg中序列重置的实现为：

--select nextval('test1_id_seq'),setVal('test1_id_seq',max_id);
select nextval('test1_id_seq'),setVal('test1_id_seq',2);//2就是上述语句查到的最大值

多次运行的结果都都是这样，说明，用最大值2重置序列后，下一次查时得到的是3，即最大值的下一个。

移植为sql server 时，重置序列使用的做法是：

alter sequence test1_id_seq restart with max_id

同样的进行上述过程：

create table test1
(
id int,
name varchar(32)
);
create sequence test1_id_seq increment by 1 minvalue 1 no maxvalue start with 1;   
insert into test1(id,name)values(1,'hhh');
insert into test1(id,name)values(2,'hhh');
SELECT * from test1;
declare @max int;
select @max = max(id)  from test1;
select @max as max_id;
declare @sql varchar(1024);
set @sql = N'alter sequence test1_id_seq restart with '+ convert(varchar(32),@max);
exec(@sql);
select next value  for test1_id_seq as next_value;

多次运行结果都如上图所示，从运行结果中可以看出，本来想得到next value可实际上得到的是最大值，为什么是最大值呢？因为restart with max_id了啊，不是最大值还能是什么。也就是说每次规定了序列的起始值，那么即时执行的是 select next value for test1_id_seq 注意是这条语句是select next value ，他仍然还是要从起始的start值开始的，而不是start_value的下一个值，这就是坑的所在！

后记：
这个bug困扰我了很长时间，一度然我怀疑是sql server出了什么幺蛾子，然而并没有。这个世界上总是有那么多你觉得莫名其妙的事情一件件又一件地理所当然的发生；总是有那么多你不认同，你觉得不可思议，你觉得不能理解的思想，最终还会有个不错的结果。（虽然不能唯结果论）我想说的是：多数情况下，你的莫名其妙是因为你不知道，不了解，不明其中奥义。