nextval and curval confusion in PostgreSQL

Question

What will happen if multiple user run the following query at the same time?

INSERT INTO "Retail"."Attributes"("AttributeId","AttributeCode","AttributeName")
VALUES(nextval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass),'COL','Color');

INSERT INTO "Retail"."AttributeDetails"  
  ("AttributeId","AttributeDetailCode","AttributeDetailName")
VALUES
(
  currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Red', 'Color Red'
),
(
  currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Blu', 'Color Blue'
), 
(
  currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Gre', 'Color Green'
);

Is this as method as reliable as SCOPE_IDENTITY() of SQL SERVER? Any suggestion will be highly appreciated.

Sequences will be as, if not more, reliable than SCOPE_IDENTITY(): http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value — , May 11 '11 at 14:27

score 4 · Answer 1 · answered May 10 '11 at 10:11

currval() is session specific. If a separate session increments your sequence, currval() will continue to return your expected value.

That said you may want to use insert returning:

insert into foo values (...) returning id;

insert into bar (foo_id, ...) values (:id, ...),  (:id, ...), ...;

score 2 · Answer 2 · answered May 10 '11 at 09:58

Based on the documentation - 'multiple sessions are guaranteed to allocate distinct sequence values'

So you will get distinct values but depending on the 'cache' setting specified while creating the sequence object, the values obtained across various concurrent sessions might not always be sequential. Read the documentation on sequences, especially the 'cache' parameter.

score 0 · Answer 3 · 2011-05-10T11:37:45.820

0

Your usage of currval is perfectly OK

I don't know SCOPE_IDENTITY() so I cannot compare the two concepts.

Btw: you don't need the cast in your statement:

currval('"Retail"."CompoundUnits_CompoundUnitId_seq"')

is enough

edited May 10 '11 at 11:37

answered May 10 '11 at 10:38

1

Not ture, you don't have to have autocommit turned off. currval is perfectly safe outside a transaction – Scott Marlowe May 10 '11 at 11:08
+1 Scott. currval() is initialized for a session, and is concurrency safe. It's the reason you cannot call it prior to using nextval(), or setval() with the boolean flag set accordingly. – Denis de Bernardy May 10 '11 at 11:17
Sequences really mess with people's heads. They're transaction safe because they are independent of all transactional semantics, kinda living outside of them. Took me a while to wrap my head around them when I got started. – Scott Marlowe May 11 '11 at 04:44

nextval and curval confusion in PostgreSQL

3 Answers3