关于postgresql序列的问题
我有一个关于 postgresql 序列的问题。
例如，对于bigserial数据类型，如果序列确实前进，则检索数字，即使插入/提交不成功，序列也不会回溯。这意味着下次我可能会对表进行插入时，这可能是序列号中的一个间隙。
我的表上有一个插入行触发器，我使用的是 psycopg2。
提前致谢。
I have a question regarding postgresql sequences.
For instance, for bigserial datatype, is it true that the sequence is advanced, then the number is retrieved and even if the insertion/committing is not successful, the sequence doesn't backtracks. Which means the next time I might be doing insertion to the table, that might be a gap in the sequence number.
Theres a before insert row trigger on my table and Im using psycopg2.
thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的，确实如此，那很好。
人们通常需要一个序列来获取表中唯一的值（通常用于 PK）
差距根本不重要。
如果您好奇：如果考虑并发性，这是自然行为。假设事务 T1 插入一行，从序列中获取 PK1，使用该值在其他表中构建另一条记录...同时（在 T1 提交之前）另一个事务 T2 在同一个表中插入一行。然后 T1 回滚并 T2 提交...
顺便说一句：如果你想要一个“无间隙”序列...首先问问自己是否真的想要这个（通常你真的不想要 - 并且要求经常指向一个概念问题）您的设计）...但如果您确实需要它，您可以阅读此。
Yes, that's true, And that's fine.
One usually wants a sequence to get values in a table that are unique (typically for a PK)
and gaps don't matter at all.
If you are curious: this is natural behaviour if one thinks about concurrency. Suppose a transaction T1 inserts a row, getting a PK1 from a sequence, uses that value to build another records in other tables... in the meantime (before T1 commits) another transaction T2 inserts a row in the same table. Then T1 rollbacks and T2 commits...
BTW: If you want a "gap-less" sequence ... first ask yourself if you really want that (usually you really don't - and requiring that frequently points to a conceptual problem in your design)... but if you really need it, you can read this.
回溯需要锁定直到完成。这会很糟糕，尤其是当 10 个表都可以使用相同的序列时。如果您想要订单，请不要使用序列，请使用窗口函数，例如 row_number()。
Backtracking would require locking until completion. This would be bad, especially when 10 tables can all utilize the same sequence. If you want an order don't use a sequence use a window function like row_number().