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

问题描述

使用Django与PostgreSQL（8.x）后端，我有一个模型，我需要跳过一个id的块，例如在发布49999之后，我希望下一个ID为70000不是50000（因为该块被保留给另一个源，其中实例被明确地添加了id - 我知道这不是一个伟大的设计，但这是我必须使用的）。 / p>

这是什么正确/最安全的地方？

我知道我可以用

  SELECT SETVAL（
（SELECT pg_get_serial_sequence（'myapp_mymodel'，'id'）），
 70000，
 false 
）; 
  

但Django何时实际从序列中拉出一个数字？
我重写MyModel.save（），调用它的超级，然后抓住一个光标，并检查与

  SELECT currval（
（SELECT pg_get_serial_sequence（'myapp_mymodel'，'id'））
）; 
  

？

我相信一个序列即使保存模型失败，也可能由django提前，所以我想确保每当它击中该数字时，它会有一个比save（）更好的地方？

PS：即使是这样的方式 - 实际上可以弄清楚save（）这样的session的currval吗？如果我抓住一个连接和游标，并执行第二个SQL语句，我不会在另一个会话中，因此没有得到一个currval？

谢谢任何指针。

编辑：我有一种感觉，这必须在数据库级（并发问题）完成，并发布相应的PostgreSQL问题 - a href =https://stackoverflow.com/questions/9341890/how-can-i-forward-a-primary-key-sequence-in-postgresql-safely>如何安全地转发PostgreSQL中的主键序列？

解决方案

我的下一个想法是不使用主键的顺序，而是总是从独立的计数器表中明确指定该ID，在使用其最新数字之前，我检查/更新，这应该是并发问题的安全。唯一的问题是，虽然我有一个地方添加了模型实例，但django或第三方应用程序的其他部分仍然可能依赖于我不想破坏的隐式ID。

但是同样的机制恰好在postgres级别很容易实现 - 我相信这是解决方案：

• 不要使用SERIAL作为主键，使用DEFAULT my_next_id（）


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


• 而不是只增加1，检查边界是否越过，如果是，则进一步增加

Using Django with a PostgreSQL (8.x) backend, I have a model where I need to skip a block of ids, e.g. after giving out 49999 I want the next id to be 70000 not 50000 (because that block is reserved for another source where the instances are added explicitly with id - I know that's not a great design but it's what I have to work with).

What is the correct/safest place for doing this?

I know I can set the sequence with

SELECT SETVAL(
    (SELECT pg_get_serial_sequence('myapp_mymodel', 'id')),
    70000,
    false
);

but when does Django actually pull a number from the sequence? Do I override MyModel.save(), call its super and then grab me a cursor and check with

SELECT currval(
    (SELECT pg_get_serial_sequence('myapp_mymodel', 'id'))
);

?

I believe that a sequence may be advanced by django even if saving the model fails, so I want to make sure whenever it hits that number it advances - is there a better place than save()?

P.S.: Even if that was the way to go - can I actually figure out the currval for save()'s session like this? if I grab me a connection and cursor, and execute that second SQL statement, wouldn't I be in another session and therefore not get a currval?

Thank you for any pointers.

EDIT: I have a feeling that this must be done at database level (concurrency issues) and posted a corresponding PostgreSQL question - How can I forward a primary key sequence in PostgreSQL safely?

解决方案

My next thought was to not use a sequence for the primary key, but rather always specify the id explicitly from a separate counter table which I check/update before using its latest number - that should be safe from concurrency issues. The only problem is that although I have a single place where I add model instances, other parts of django or third-party apps may still rely on an implicit id, which I don't want to break.

But that same mechanism happens to be easily implemented on postgres level - I believe this is the solution:

• 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

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