On Sat, Aug 2, 2008 at 11:04 AM, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> 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/
