Skip site navigation (1) Skip section navigation (2)

Does setval(nextval()+N) generate unique blocks of IDs?

From: Craig James <cjames(at)emolecules(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Does setval(nextval()+N) generate unique blocks of IDs?
Date: 2012-08-20 23:32:27
Message-ID: CAFwQ8re6hFodxhfRcsrpywegFfRAVd9a3fe=9BOXL2dtezMEAQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Is seq.setval() "non transactional" in the same sense as seq.nextval()
is?  More specifically, suppose I sometimes want to get IDs one-by-one
using nextval(), but sometimes I want a block of a thousand IDs.  To
get the latter, I want to do this:

    select setval('object_id_seq', nextval('object_id_seq') + 1000, false);

Now suppose two processes do this simultaneously.  Maybe they're in
transactions, maybe they're not.  Are they guaranteed to get distinct
blocks of IDs?  Or is it possible that each will execute nextval() and
get N and N+1 respectively, and then do setval() to N+1000 and N+1001,
resulting in two overlapping blocks.

If the answer is, "This won't work," then what's a better way to do this?

Thanks,
Craig


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2012-08-21 00:10:00
Subject: Re: Does setval(nextval()+N) generate unique blocks of IDs?
Previous:From: delongboyDate: 2012-08-20 20:51:09
Subject: Re: Increasing WAL usage followed by sudden drop

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group