How can I forward a primary key sequence in PostgreSQL safely?

Question

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: 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)...

Answer 1

I believe I've found a way:

• 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