如何安全地在PostgreSQL中转发主键序列？ [英] How can I forward a primary key sequence in PostgreSQL safely?

问题描述

使用PostgreSQL（8.x），我需要自动跳过一段序列号，例如给出49999后，我希望下一个id为70000而不是50000

Using PostgreSQL (8.x), I need to skip a block of sequence numbers automatically, e.g. after giving out 49999 I want the next id to be 70000 not 50000

这样做的正确/最安全方法是什么？

What is the correct/safest way of doing this?

NB：我最初发布了一个非常类似的问题如何在Django中安全地转发主键序列？，但是我觉得这必须在数据库级别完成（并发问题）...

NB: I originally posted a very similar question How can I forward a primary key sequence in Django safely? but I have the feeling that this must be done at database level (concurrency issues)...

推荐答案

我相信我找到了一种方法：

I believe I've found a way:

• 不要使用对于主键为SERIAL，请使用DEFAULT my_next_id（）


• 遵循与单级无缝序列相同的逻辑- http://www.varlena.com/GeneralBits/130.php -my_next_id（）进行更新，然后进行选择


• 检查边界是否已过，而不是仅仅增加1，否则就进一步增加

• Don't use SERIAL for the primary key, use DEFAULT my_next_id()
• Follow the same logic as for "single level gapless sequence" - http://www.varlena.com/GeneralBits/130.php - my_next_id() does an update followed by a select
• Instead of just increasing by 1, check if a boundary was crossed and if so, increase even further

这篇关于如何安全地在PostgreSQL中转发主键序列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！