Re: incrementing updates and locks

On 09/16/2010 10:54 PM, Aras Angelo wrote:
> Hello All
>
> I have a column in my table which is incrementally updated. I cant set
> this field to be a SERIAL because the value is not assigned at the
> time of the INSERT, it is assigned later by the system by an UPDATE.
> Whats the best way to achieve this by means of performance?

It sounds like what you need is a sequence.
http://www.postgresql.org/docs/8.3/static/sql-createsequence.html

There is one gotcha - if you obtain a value from the sequence, and the
transaction is rolled back, the value is *not* reissued; so, you could
get gaps.  If that's unacceptable, you might try indexing that field; I
don't know if max uses them, but it'd be worth a try.

--
  _____________________________________________________
   Daniel J. Summers
   Owner, DJS Consulting          Albuquerque, NM, USA
   ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
   E-mail        ~ daniel@djs-consulting.com
   Website       ~ http://djs-consulting.com
   Tech Blog     ~ http://techblog.djs-consulting.com
   Personal Blog ~ http://daniel.summershome.org
  _____________________________________________________

GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w$ !O M--
V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e h---- r+++ z++++