16

I mean like thousands users in time updating values in database?

juk
  • 2,179
  • 4
  • 19
  • 25

4 Answers4

25

Yes, nextval is safe to use from multiple concurrently operating transactions. That is its purpose and its reason for existing.

That said, it is not actually "thread safe" as such, because PostgreSQL uses a multi-processing model not a multi-threading model, and because most client drivers (libpq, for example) do not permit more than one thread at a time to interact with a single connection.

You should also be aware that while nextval is guaranteed to return distinct and increasing values, it is not guaranteed to do so without "holes" or "gaps". Such gaps are created when a generated value is discarded without being committed (say, by a ROLLBACK) and when PostgreSQL recovers after a server crash.

While nextval will always return increasing numbers, this does not mean that your transactions will commit in the order they got IDs from a given sequence in. It's thus perfectly normal to have something like this happen:

Start IDs in table: [1 2 3 4]
1st tx gets ID 5 from nextval()
2nd tx gets ID 6 from nextval()
2nd tx commits:     [1 2 3 4 6]
1st tx commits:     [1 2 3 4 5 6]

In other words, holes can appear and disappear.

Both these anomalies are necessary and unavoidable consequences of making one nextval call not block another.

If you want a sequence without such ordering and gap anomalies, you need to use a gapless sequence design that permits only one transaction at a time to have an uncommitted generated ID, effectively eliminating all concurrency for inserts in that table. This is usually implemented using SELECT FOR UPDATE or UPDATE ... RETURNING on a counter table.

Search for "PostgreSQL gapless sequence" for more information.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Craig, does this means that it may return the same value for two concurent transactions?! and consequently a "duplicate key value violation" in case we use it to calculate the id? thanks in advance – Tarik Feb 01 '17 at 13:32
  • @Tarik No, its entire purpose is that it can _never_ return the same value multiple times, in the same or different xacts. But it doesn't guarantee anything about order or gaps. – Craig Ringer Feb 01 '17 at 21:01
4

Yes it is threadsafe.

From the manual:

nextval

Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value.

(Emphasis mine)

  • Do they assume clients are from different machines? I just triggered an error using two browser inserting rows simultaneosly! – juk Jan 08 '13 at 13:38
  • @juk: what kind of error? calling `nextval()` can be called from any number of transactions. –  Jan 08 '13 at 14:02
  • 1
    @juk You appear to have some fundamental misunderstandings about how client/server architectures work. PostgreSQL doesn't care where a client is from - localhost, a LAN host, or anywhere on the Internet that's allowed to connect. To PostgreSQL it's just a connection - another session, with all the same rules. In this case, I doubt the PostgreSQl connections are from different machines anyway; the *browser sessions* are, but they'll be connecting to a server and that server will be making PostgreSQL connections. The browsers almost certainly don't connect directly to PostgreSQL. – Craig Ringer Jan 08 '13 at 14:03
  • @CraigRinger ofcourse I abstracted "browser", it goes via tornadoweb and psycopg module – juk Jan 08 '13 at 14:11
  • @juk ... in which case all the connections come from the same host anyway, not that it matters. – Craig Ringer Jan 08 '13 at 14:15
2

Yes: http://www.postgresql.org/docs/current/static/functions-sequence.html

It wouldn't be useful otherwise.

Edit: Here is how you use nextval and currval:

nextval returns a new sequence number, you use this for the id in an insert on the first table

currval returns the last sequence number obtained by this session, you use that in foreign keys to reference the first table

each call to nextval returns another value, don't call it twice in the same set of inserts.

And of course, you should use transactions in any multiuser code.

Peter Wooster
  • 6,009
  • 2
  • 27
  • 39
  • Then how comes i get errors if i add data simultaneously from two different browsers: IntegrityError: insert or update on table "category" violates foreign key constraint "category_category_id_fkey" DETAIL: Key (category_id)=(60) is not present in table "book". CONTEXT: SQL statement "INSERT INTO schemas.category ( category_id, name ) VALUES ( in_category_id, in_category )" PL/pgSQL function "book_add" line 39 at SQL statement – juk Jan 08 '13 at 13:35
  • If you have a specific problem you're trying to debug, put the details in the question please. – David Aldridge Jan 08 '13 at 13:59
  • 3
    @juk There's certain to be a bug in your code. Since we can't see your code, I can't really say more. Update your question with the code you're running and your table definitions, maybe we can help. Hint: The error says you're violating a **foreign key constraint** not a **unique constraint** so I'd say you're trying to refer to a row that doesn't exist. Maybe you expect `nextval` to return the same ID repeatedly within a single transaction? It doesn't do that. When you update your question include your PostgreSQL version and *all* the code + full text of error messages. – Craig Ringer Jan 08 '13 at 13:59
  • I've added some more discussion on how you use these, they are basically the same as the Oracle sequence, but with slightly different syntax. – Peter Wooster Jan 08 '13 at 15:00
  • Actually at this point juk should create a new questions, as this one has been answered definitively. In his new question he should strive to create a reproduceable test case others can use to get the same results. Note that often in designing a reproduceable test case, the answer you are seeking becomes obvious and you then don't need to post the question. Should this happen he should probably post it anyway along with his own answer so others can learn from this. – Scott Marlowe Jan 09 '13 at 03:58
1

This poster asked a different question on the same flawed code. here Point is: he does not seem to know how foreign keys work, and has them reversed (a sequence functioning as a foreign key is kind of awkward IMHO)

BTW: This is should be a comment, not an answer; but I can't comment yet.

Community
  • 1
  • 1
joop
  • 41
  • 1