Postgres: incrementing a counter field

Question

How does one increment a field in a database such that even if if a thousands connections to the database try to increment it at once -- it will always be 1000 at the end (if started from zero).

I mean, so that no two connections increment the same number number resulting in a lost increment.

How do you synchronize and make sure the data is consistent? is there a must for a stores procedure for this? database "locking"? how is that done?

update the_table set the_column = the_column + 1 - they will wait in the order they issued the update statement until the previous update is committed. — user330315
– user330315, Commented Jul 20, 2015 at 21:09
Are you looking for a "gapless sequence"? Are you aware of the built-in sequence support but don't want to use it? — Craig Ringer
– Craig Ringer, Commented Jul 22, 2015 at 4:49

Community · Accepted Answer · 2017-05-23 11:58:13Z

3

What you're looking for is a Postgres SEQUENCE.

You call nextval('sequence_name') to get the next number in the sequence.

According to the docs, sequences are designed with concurrency in mind:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

EDIT:

If you're looking for a gapless sequence, in 2006 someone posted a solution to the PostgreSQL mailing list: http://www.postgresql.org/message-id/[email protected]. It appears there's also a lengthy discussion on locking, etc.

The gapless-sequence question was also asked on SO even though there was never an accepted answer: postgresql generate sequence with no gap

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jul 20, 2015 at 21:18

Travis

2,0481 gold badge21 silver badges37 bronze badges

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

2 Comments

Craig Ringer Over a year ago

Actually I suspect it isn't. If a connection rolls back before committing, the DB server is restarted, etc, there will be gaps. I suspect they're looking for a way to avoid this.

Travis Over a year ago

Hm. You may be right. See my edits above. If two connections increment the same number, consistency is lost and there is a gap. I was thinking consistency was the main concern, not the gap.

Collectives™ on Stack Overflow

Postgres: incrementing a counter field

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related