How can I forward a primary key sequence in Django

问题:

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?

回答1:

As I haven't found an "automated" way of doing this yet, I'm thinking of the following workaround - it would be feasible for my particular situation:

1. Set the sequence with a MAXVALUE 49999 NO CYCLE
2. When 49999 is reached, the next save() will run into a postgres error
3. Catch that exception and reraise as a form error "you've run out of numbers, please reset to the next block then try again"
4. Provide a view where the user can activate the next block, i.e. execute "ALTER SEQUENCE my_seq RESTART WITH 70000 MAXVALUE 89999"

I'm uneasy about doing the restart automatically when catching the exception:

try:
    instance.save()
except RunOutOfIdsException:
    restart_id_sequence()
    instance.save()

as I fear two concurrent save()'s running out of ids will lead to two separate restarts, and a subsequent violation of the unique constraint. (basically same concept as original problem)

回答2:

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