Skip site navigation (1) Skip section navigation (2)

Re: SERIAL order and INSERT order

From: l1(at)nym(dot)hush(dot)com
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: SERIAL order and INSERT order
Date: 2011-06-15 12:34:46
Message-ID: 20110615123446.975DA14DBCF@smtp.hushmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks.   

The issue is we have some externally driven constraints on this 
particular column. Anyway, I can enforce ordering another way.

On Wed, 15 Jun 2011 07:27:53 -0400 Bob McConnell 
<rmcconne(at)lightlink(dot)com> wrote:
>Thomas Kellerer wrote:
>> l1(at)nym(dot)hush(dot)com, 14.06.2011 20:30:
>> 
>>> Are there any cases where an observer might see a row with 
>serial
>>> number 2 but not yet see serial number 1, perhaps because 
>thread 1
>>> hasn't completed its insert yet?  For example SELECT * WHERE
>>> seqnumber<  3; would return only a row with seqnumber=2?
>> 
>> It is never a good idea to rely on the numeric ordering of a 
>generated 
>> PK column. If you really need to know in which order rows were 
>inserted 
>> you should use a timestamp that is recording that time (although 
>I 
>> believe the resolution of a timestamp column might be not fine 
>enough..)
>
>Any time you have multiple threads, processes or processors 
>accessing 
>the same database, race conditions like this are not only possible 
>but 
>very likely. It is the nature of the multi-processing beast. You 
>cannot 
>guarantee that every insert will be completed before the next one, 
>since 
>you cannot guarantee that every scheduler involved will recognize 
>that 
>the inserts should be atomic. In fact, most of them won't. In the 
>case 
>of multiple computers, you cannot even guarantee that the CPU in 
>the 
>first one in will be as fast as the next one. There are simply too 
>many 
>variables that you cannot control.
>
>The seqnumbers will be assigned at some point in each transaction, 
>in 
>the order that they reach that point. But the transactions are not 
>
>complete until they are committed, and that cannot be guaranteed 
>to be 
>in the same order as the assignments. The results of any query 
>that is 
>processed between those two events will reflect the state of the 
>database at that point in time. So yes, there may be a missing 
>seqnumber 
>or two on occasion. Any expectations to the contrary are 
>unrealistic.
>
>Bob McConnell
>N2SPP
>
>-- 
>Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-novice


pgsql-novice by date

Next:From: Sindile BidlaDate: 2011-06-15 15:41:56
Subject: Folder missing
Previous:From: Bob McConnellDate: 2011-06-15 11:27:53
Subject: Re: SERIAL order and INSERT order

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group