From: Daniel J. Summers <daniel.lists@djs-consulting.com>
To: pgsql-admin@postgresql.org
Subject: Re: incrementing updates and locks
Date: Thu, 16 Sep 2010 23:09:00 +0000
Message-ID: <4C92A38C.3020209@djs-consulting.com> (raw)
In-Reply-To: <AANLkTinQ-A9oPExCgmwWv5br9+kmvnZVCBNhdLzED_qK@mail.gmail.com>
References: <AANLkTinQ-A9oPExCgmwWv5br9+kmvnZVCBNhdLzED_qK@mail.gmail.com>
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++++
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: daniel.lists@djs-consulting.com
Subject: Re: incrementing updates and locks
In-Reply-To: <4C92A38C.3020209@djs-consulting.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox