Обсуждение: incrementing updates and locks

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? Should i set the MAX variable in a different table, and query that table and assign the next value to the actual table every time there is an UPDATE going? That means a SELECT to the max number holder table, UPDATE to the actual table, and an UPDATE to the max_holder table again. That probably would slow down the process but i cant think of a better way.

Lets say a client updates 30 in a batch, i dont think i can do one SELECT max_number and increment that number with the programming language independent from the DB because several clients access the database synchronously.

Also should i be worried about LOCKING the tables, or UPDATE statements already does a lock itself?

Sorry if i could not make it clear enough.

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

On 9/16/10 3:54 PM, Aras Angelo wrote:
> Hello All
>
> I have a column in my table which is incrementally updated.

Try to give us more details...

Does the column need have contiguous values or are "gaps" ok?  That is, does it have to be 1,2,3,4,...,N-1,N or is it
okto have something like 1,3,4,7,...,M (where M>N) for N rows? 

Is the value updated every time the row is changed, or is it set once only?

If gaps are OK, then a sequence is a simple answer.

Craig

Daniel, Craig

The gaps are not really expected. It is set once only.
Its about printing packing slips for ecommerce orders. We have the ORDER ID sequence, but so many different stations are accessing these orders, if my station print the next 100 orders from the que, id like to give them values starting from  MAX(print_number_sequence so far) AND  +1, +2, +3, .... +100.

I hope this clears it better. I think a sequence can work. My concern was performance, as in the actual programming LOOP, querying the max field, assigning the row number, reissuing the max field. A sequence i guess, would perform better than a regular table index?

Dumb i am.. nextval() already issued the next one to the sequence.
I probably dont need a separate table.

On 9/16/10 4:17 PM, Aras Angelo wrote:
> Daniel, Craig
>
> The gaps are not really expected. It is set once only.
> Its about printing packing slips for ecommerce orders. We have the ORDER ID sequence, but so many different stations
areaccessing these orders, if my station print the next 100 orders from the que, id like to give them values starting
from MAX(print_number_sequence so far) AND  +1, +2, +3, .... +100. 
>
> I hope this clears it better. I think a sequence can work. My concern was performance, as in the actual programming
LOOP,querying the max field, assigning the row number, reissuing the max field. A sequence i guess, would perform
betterthan a regular table index? 

Sequences are very fast. Just do something like this:

   update mytable set order_id = nextval('order_id_seq') where ...

Using this technique, you will only get gaps in the sequence if something goes wrong and the transaction is rolled
back. But given what you have told us about your application, it is probably not very important if a few ORDER ID
numbersare missing. 

Craig

> On Thu, Sep 16, 2010 at 4:06 PM, Craig James <craig_james@emolecules.com <mailto:craig_james@emolecules.com>> wrote:
>
>     On 9/16/10 3:54 PM, Aras Angelo wrote:
>
>         Hello All
>
>         I have a column in my table which is incrementally updated.
>
>
>     Try to give us more details...
>
>     Does the column need have contiguous values or are "gaps" ok?  That is, does it have to be 1,2,3,4,...,N-1,N or
isit ok to have something like 1,3,4,7,...,M (where M>N) for N rows? 
>
>     Is the value updated every time the row is changed, or is it set once only?
>
>     If gaps are OK, then a sequence is a simple answer.
>
>     Craig
>
>
>     --
>     Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org <mailto:pgsql-admin@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-admin
>
>

Craig James  wrote:

> given what you have told us about your application, it is probably
> not very important if a few ORDER ID numbers are missing.

I'm not so sure.  If these are considered accounting records which
may be audited by a CPA firm, I would definitely check with the
accountants before assuming that's OK.  I spent four years working
for a CPA firm, participating in audits where computer expertise was
needed; these guys considered it a major problem if there were gaps
in the sequence numbers of financial transactions without a signed
letter bound into the official books to explain each gap.  That's
probably related to the fact that in most of the cases of
embezzlement I heard about, numbering gaps in the sales records were
the tip-off that something was wrong.  (If there's no record of the
sale, who's going to miss the money from it?)

Perhaps you have other controls which make that one irrelevant, but I
would check....

-Kevin