pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedincrementing updates and locks
7+ messages / 4 participants
[nested] [flat]
* incrementing updates and locks
@ 2010-09-16 22:54 Aras Angelo <araskoktas@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Aras Angelo @ 2010-09-16 22:54 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: incrementing updates and locks
@ 2010-09-16 23:06 Craig James <craig_james@emolecules.com>
parent: Aras Angelo <araskoktas@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Craig James @ 2010-09-16 23:06 UTC (permalink / raw)
To: pgsql-admin
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 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: incrementing updates and locks
@ 2010-09-16 23:09 Daniel J. Summers <daniel.lists@djs-consulting.com>
parent: Aras Angelo <araskoktas@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Daniel J. Summers @ 2010-09-16 23:09 UTC (permalink / raw)
To: pgsql-admin
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++++
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: incrementing updates and locks
@ 2010-09-16 23:17 Aras Angelo <araskoktas@gmail.com>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 2 replies; 7+ messages in thread
From: Aras Angelo @ 2010-09-16 23:17 UTC (permalink / raw)
To: pgsql-admin
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:
>
>> 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 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: incrementing updates and locks
@ 2010-09-16 23:21 Aras Angelo <araskoktas@gmail.com>
parent: Aras Angelo <araskoktas@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Aras Angelo @ 2010-09-16 23:21 UTC (permalink / raw)
To: pgsql-admin
Dumb i am.. nextval() already issued the next one to the sequence.
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:
>>
>>> 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 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
>>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: incrementing updates and locks
@ 2010-09-17 00:04 Craig James <craig_james@emolecules.com>
parent: Aras Angelo <araskoktas@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Craig James @ 2010-09-17 00:04 UTC (permalink / raw)
To: 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 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?
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 numbers are 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 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 <mailto:pgsql-admin@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: incrementing updates and locks
@ 2010-09-17 02:53 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2010-09-17 02:53 UTC (permalink / raw)
To: craig_james@emolecules.com; 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-09-17 02:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-16 22:54 incrementing updates and locks Aras Angelo <araskoktas@gmail.com>
2010-09-16 23:06 ` Craig James <craig_james@emolecules.com>
2010-09-16 23:17 ` Aras Angelo <araskoktas@gmail.com>
2010-09-16 23:21 ` Aras Angelo <araskoktas@gmail.com>
2010-09-17 00:04 ` Craig James <craig_james@emolecules.com>
2010-09-16 23:09 ` Daniel J. Summers <daniel.lists@djs-consulting.com>
2010-09-17 02:53 Re: incrementing updates and locks Kevin Grittner <Kevin.Grittner@wicourts.gov>
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