Re: Advice on implementing counters in postgreSQL - Mailing list pgsql-general

From Berend Tober
Subject Re: Advice on implementing counters in postgreSQL
Date
Msg-id 4894F057.1000202@ct.metrocast.net
Whole thread Raw
In response to Advice on implementing counters in postgreSQL  ("Marco Bizzarri" <marco.bizzarri@gmail.com>)
Responses Re: Advice on implementing counters in postgreSQL  ("Marco Bizzarri" <marco.bizzarri@gmail.com>)
List pgsql-general
Marco Bizzarri wrote:
 > Hi all.
 >
 > I need to keep a numer of counters in my application; my
counters are
 > currently stored in a table:
 >
 > name | next_value | year
 >
 >
 > The counters must be progressive numbers with no holes in between
 > them, and they must restart from 1 every year. What I've done
so far
 > is to access them while in SERIALIZABLE ISOLATION LEVEL, with the
 > following:
 >
 > SELECT next_value FROM counters WHERE name = 'name' for update;
 > UPDATE counters SET next_value = next_value + 1 WHERE name =
'name';
 >...
 > 2) while this works, it has the unfortunate behaviour to cause
 > conflict between concurrent transactions; so, one of them has
to be
 > restarted and redone from scratch. Is there a way to avoid this
 > behaviour? maybe with lock to tables?


The way I understand the documentation at

"http://www.postgresql.org/docs/8.3/static/transaction-iso.html"

and

'http://www.postgresql.org/docs/current/static/explicit-locking.html',

you should not have to use the serial isolation level.

I would define the counter table so as to hold the last-used
value, rather that the "next" value, and then do the UPDATE first.

As a consequence, assuming all this happens within a transaction
  of course, the SELECT FOR UPDATE syntax is not required either
because the UPDATE will grab a lock on the row and block other
updates until the transaction is finished. That is, concurrency
is protected and you don't have to restart any transactions
because subsequent transactions will just wait until the first
one finishes due to nature of the lock automatically acquired by
the initial UPDATE statement.






pgsql-general by date:

Previous
From: "Ismael ...."
Date:
Subject: Re: issues with java driver setDate() on function call
Next
From: "Rodrigo E. De León Plicet"
Date:
Subject: Re: Is there any reason why "edit PostgreSQL.conf should be on my menu"