Message-ID: <4C92A38C.3020209@djs-consulting.com>
Date: Thu, 16 Sep 2010 23:09:00 +0000
From: "Daniel J. Summers" <daniel.lists@djs-consulting.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.12) Gecko/20100826 Thunderbird/3.0.7
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: incrementing updates and locks
References: <AANLkTinQ-A9oPExCgmwWv5br9+kmvnZVCBNhdLzED_qK@mail.gmail.com>
In-Reply-To: <AANLkTinQ-A9oPExCgmwWv5br9+kmvnZVCBNhdLzED_qK@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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++++