不受交易影响的序列？
我有一张表，
create table testtable(
testtable_rid serial not null,
data integer not null,
constraint pk_testtable primary key(testtable_rid)
);
所以可以说我做了大约20次的代码：
begin;
insert into testtable (data) values (0);
rollback;
然后我做到了
begin;
insert into testtable (data) values (0);
commit;
，最后是
select * from testtable
Result: row0: testtable_rid=21 | data=0 Expected result: row0: testtable_rid=1 | data=0
您所看到的，序列似乎不会受到事务回滚的影响。他们继续递增，好像交易是在进行的，然后删除了行。有什么方法可以防止序列以这种方式行事？
I have a table
create table testtable(
testtable_rid serial not null,
data integer not null,
constraint pk_testtable primary key(testtable_rid)
);
So lets say I do this code about 20 times:
begin;
insert into testtable (data) values (0);
rollback;
and then I do
begin;
insert into testtable (data) values (0);
commit;
And finally a
select * from testtable
Result: row0: testtable_rid=21 | data=0 Expected result: row0: testtable_rid=1 | data=0
As you can see, sequences do not appear to be affected by transaction rollbacks. They continue to increment as if the transaction was committed and then the row was deleted. Is there some way to prevent sequences from behaving in this way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
回滚序列不是一个好主意。想象一下同时发生的两笔交易，每个交易都使用序列作为唯一的ID。如果第二笔交易进行了，并且第一个事务向后回滚，则第二个交易将用“ 2”插入一行，而第一个序列将序列滚回“ 1”。
如果然后再次使用该序列，则序列的值将成为“ 2”，这可能导致独特的约束问题。
It would not be a good idea to rollback sequences. Imagine two transactions happening at the same time, each of which uses the sequence for a unique id. If the second transaction commits and the first transaction rolls back, then the second inserted a row with "2" while the first rolls the sequence back to "1".
If that sequence is then used again, the value of the sequence will become "2" which could lead to a unique constraint problem.
不，没有。请参阅
No, there isn't. See the note at the bottom of this page. It's a bad idea to do something like that anyway. If you have two transactions running at the same time, each inserting one row, you want them to insert rows with different IDs.