CODEWITHSUNDEEP
sqlpostgresqlauto-increment
woryzower
woryzower

Reputation: 976

Postgresql wrong auto-increment for serial

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.

Upvotes: 4

Views: 4091

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 126971

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.

Upvotes: 3

Sjoerd
Sjoerd

Reputation: 75568

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.

Upvotes: 8

Related Questions