CODEWITHSUNDEEP
postgresqlsequenceauto-increment
Thomas
Thomas

Reputation: 1022

Are PostgreSQL functions atomic? (ID generation)

Let' say I have the following Stored Procedure in PostgreSQL. (Please note that this is not real code. I know this can be done in another and better way, it is just an example to illustrate my question)

CREATE FUNCTION testFunction( ) RETURNS integer AS $$
DECLARE iNewID integer;
BEGIN
  SELECT CurrentID INTO iNewID FROM TestTable; // statement 1

  iNewID := iNewID + 1;

  UPDATE TestTable SET CurrentID=iNewID;  // statement 2

  RETURN iNewID;
END; $$
LANGUAGE PLPGSQL;

Let's say that two different users run this at almost the same time. Can they get the same ID returned?

I will try to explain in other words. In the code I have commented two statements. If two users run this at the same time. In what order is the statement called. Can I be sure it will be like this:

Or could it be like this (resulting in same ID):

I hope you understand my question. I have really tried to search the net for an answer to this, but with no luck. This could be because I don't have any name for this? What is it called?

Thanks a lot for any help.

Upvotes: 0

Views: 1082

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324445

Let's say that two different users run this at almost the same time. Can they get the same ID returned?

Yes, of course. It's just the same as if you ran it as two separate statements outside a transaction, doing the addition in your app.

I wrote about this recently.

Use a SEQUENCE to ensure unique IDs. The SERIAL pseudo-type makes this easier. Note that the IDs aren't guaranteed to be gapless; it's normal to have IDs like: 1, 3, 4, 5, 6, 9, 10, 11, 14, .. if transactions roll back, the server is restarted, etc.

If that's a problem for you, you'll want to instead do:

SELECT CurrentID INTO iNewID FROM TestTable FOR UPDATE;
... blah blah ...

... which will lock the row so other transactions can't update it until the current one commits or rolls back. That can actually be simplified using PostgreSQL-specific features into:

  UPDATE TestTable
  SET CurrentID = iNewID
  RETURNING CurrentID;

which you can then wrap in an SQL function or run in PL/PgSQL with RETURN QUERY.

Upvotes: 3

Related Questions