Thread: 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.
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?
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?
On Thu, Sep 16, 2010 at 4:06 PM, Craig James <craig_james@emolecules.com> wrote:
On 9/16/10 3:54 PM, Aras Angelo wrote:Try to give us more details...Hello All
I have a column in my table which is incrementally updated.
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 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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Dumb i am.. nextval() already issued the next one to the sequence.
I probably dont need a separate table.
I probably dont need a separate table.
On Thu, Sep 16, 2010 at 4:17 PM, Aras Angelo <araskoktas@gmail.com> 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 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?On Thu, Sep 16, 2010 at 4:06 PM, Craig James <craig_james@emolecules.com> wrote:On 9/16/10 3:54 PM, Aras Angelo wrote:Try to give us more details...Hello All
I have a column in my table which is incrementally updated.
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 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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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