Re: SERIAL order and INSERT order

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