Search Postgresql Archives

Advice on implementing counters in postgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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';

of course, if I do not find the counter, I create it (which
automatically happens at the begin of a new year).

This seems to work to me, but I've two questions:

1) is there any scenario which I'm missing here and which could lead
me to troubles? Deadlocks excluded.


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?


Thanks you all for your attention

Regards
Marco

-- 
Marco Bizzarri
http://iliveinpisa.blogspot.com/


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux