Re: Advice on implementing counters in postgreSQL

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/