Postgresql wrong auto-increment for serial

Question

I have a problem on postgresql which I think there is a bug in the postgresql, I wrongly implement something.

There is a table including colmn1(primary key), colmn2(unique), colmn3, ...

After an insertion of a row, if I try another insertion with an existing colmn2 value I am getting a duplicate value error as I expected. But after this unsuccesful try, colmn1's next value is incremented by 1 although there is no insertion so i am getting rows with id sequences like , 1,2,4,6,9.(3,5,6,7,8 goes for unsuccessful trials).

I need help from the ones who can explain this weird behaviour.

This information may be useful: I used "create unique index on tableName (lower(column1)) " query to set unique constraint.

Sjoerd · Accepted Answer · 2012-09-14 11:06:00Z

8

See the PostgreSQL sequence FAQ:

Sequences are intended for generating unique identifiers — not necessarily identifiers that are strictly sequential. If two concurrent database clients both attempt to get a value from a sequence (using nextval()), each client will get a different sequence value. If one of those clients subsequently aborts their transaction, the sequence value that was generated for that client will be unused, creating a gap in the sequence.

This can't easily be fixed without incurring a significant performance penalty. For more information, see Elein Mustein's "Gapless Sequences for Primary Keys" in the General Bits Newsletter.

answered Sep 14, 2012 at 11:06

Sjoerd

75.9k16 gold badges140 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Heikens · Accepted Answer · 2012-09-14 12:44:52Z

3

From the manual:

Important: Because sequences are non-transactional, changes made by setval are not undone if the transaction rolls back.

In other words, it's normal to have gaps. If you don't want gaps, don't use a sequence.

answered Sep 14, 2012 at 12:44

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

Postgresql wrong auto-increment for serial

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related