Thread: Advice on implementing counters in postgreSQL
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/
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'; If you're using a sufficiently recent version of Pg you can use: UPDATE counters SET next_value = next_value + 1 WHERE name = 'name' RETURNING next_value; instead, which is slightly nicer. It'll return the *new* value of `next_value', so you'd have to make a few tweaks. -- Craig Ringer
Thanks for the advice, Craig. I'm on a number of different PostgreSQL versions, ranging from 7.4 to 8.3, so I've to retain, where possible, compatibility with older versions. Is this better on a transaction/serialization point of view? Regards Marco On Sat, Aug 2, 2008 at 10:19 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > 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'; > > If you're using a sufficiently recent version of Pg you can use: > > UPDATE counters > SET next_value = next_value + 1 > WHERE name = 'name' > RETURNING next_value; > > instead, which is slightly nicer. It'll return the *new* value of > `next_value', so you'd have to make a few tweaks. > > -- > Craig Ringer > -- Marco Bizzarri http://iliveinpisa.blogspot.com/
Marco Bizzarri wrote: > Thanks for the advice, Craig. > > I'm on a number of different PostgreSQL versions, ranging from 7.4 to > 8.3, so I've to retain, where possible, compatibility with older > versions. > > Is this better on a transaction/serialization point of view? As far as I know it's not significantly different, though I expect it'd be somewhat more efficient. However, support for UPDATE ... RETURNING was only added in 8.2 (or somewhere around there) anyway, so if you need to work with old versions like 7.4 it's no good to you anyway. I take it there's no way you can present the gapless identifiers at the application level, leaving the actual tables with nice SEQUENCE numbering? Or, alternately, insert them by timestamp/sequence (leaving the user-visible ID null) then have another transaction come back and assign them their gapless numeric identifiers in a single simple pass later? You're really going to suffer on concurrency if you have to acquire values from a gapless sequence as part of a transaction that does much other work. -- Craig Ringer
On Sat, Aug 2, 2008 at 11:04 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > Marco Bizzarri wrote: >> Thanks for the advice, Craig. >> >> I'm on a number of different PostgreSQL versions, ranging from 7.4 to >> 8.3, so I've to retain, where possible, compatibility with older >> versions. >> >> Is this better on a transaction/serialization point of view? > > As far as I know it's not significantly different, though I expect it'd > be somewhat more efficient. However, support for UPDATE ... RETURNING > was only added in 8.2 (or somewhere around there) anyway, so if you need > to work with old versions like 7.4 it's no good to you anyway. > > I take it there's no way you can present the gapless identifiers at the > application level, leaving the actual tables with nice SEQUENCE > numbering? Or, alternately, insert them by timestamp/sequence (leaving > the user-visible ID null) then have another transaction come back and > assign them their gapless numeric identifiers in a single simple pass later? > You're really going to suffer on concurrency if you have to acquire > values from a gapless sequence as part of a transaction that does much > other work. Well, the sequence must be gapless, because it is an implementation of a law regarding how documents must be recorded when they are received or sent in a public administration. I can accept a "degraded" performance in this topic, considering that usually, I've between 200 and 1000 documents recorded (i.e. numbered) in a day, which is not such a great number. However, I would avoid as much as possible serialization errors, which would force me to repeat the transaction. I'm experimenting with LOCK counters IN EXCLUSIVE MODE; it seems it is able to rip me of thos serialization errors. Do you see any problems in this? Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/
On Sat, Aug 02, 2008 at 09:23:31AM +0200, 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. Here's a backward-compatible way to do this: http://www.varlena.com/GeneralBits/130.php Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
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.
On Sat, Aug 2, 2008 at 5:11 PM, David Fetter <david@fetter.org> wrote: > On Sat, Aug 02, 2008 at 09:23:31AM +0200, 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. > > Here's a backward-compatible way to do this: > > http://www.varlena.com/GeneralBits/130.php > > Cheers, > David. > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > David, thanks for pointing me to such a complete solution. These work both on READ-COMMITTED and SERIALIZABLE isolation levels, am I right? Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/
On Sun, Aug 3, 2008 at 1:40 AM, Berend Tober <btober@ct.metrocast.net> wrote: > > > 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. > Yes, I'm considering moving away from serializable; the problem is that I have to explore all the implications of this on my code. Up to now, I wrote considering a serializable level, so I think I should do quite a review to be sure about it. Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/
On Sun, Aug 3, 2008 at 1:50 AM, Marco Bizzarri <marco.bizzarri@gmail.com> wrote: > On Sun, Aug 3, 2008 at 1:40 AM, Berend Tober <btober@ct.metrocast.net> wrote: >> >> >> 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. >> > > Yes, I'm considering moving away from serializable; the problem is > that I have to explore all the implications of this on my code. Up to > now, I wrote considering a serializable level, so I think I should do > quite a review to be sure about it. A fairly simple test shows that you can do this in read committed: S1: # show transaction_isolation; read committed (setup a table for the value) # create table t (i int); # insert into t values (5); S1: # begin; S1: # update t set i=i+1; S2: # update t set i=i+1; (S2 now waits for S1) S1: # select i from t; 6 S1: # commit; (S2 now can continue...) S2: # select i from t; 7 S2: # commit;