We are having some strange behavior with PostgreSQL 7.1.3 where rows 
are inserted with a timestamp that is out of order... here is the 
table description and a few rows:


                             Table "event_page"
   Attribute   |           Type           |             Modifier
--------------+--------------------------+----------------------------------
  user_id      | oid                      |
  session_id   | character varying(24)    |
  course_ident | character varying(128)   |
  page         | character varying(32)    |
  date         | timestamp with time zone | default "timestamp"('now'::text)


  69714 |   61381 | yVLsSYYpZIa1G54VnDY7qcz2 | COG_001_ELEARNING | 
15.html | 2002-01-31 18:07:21+08
  69715 |   61381 | yVLsSYYpZIa1G54VnDY7qcz2 | COG_001_ELEARNING | 
16.html | 2002-01-31 18:07:40+08
  69717 |   61453 | uZuNbGXsWnXXRECH6PGeFdtf | COG_001_ELEARNING | 
1.html  | 2002-01-31 18:08:14+08
  69718 |   61453 | uZuNbGXsWnXXRECH6PGeFdtf | COG_001_ELEARNING | 
15.html | 2002-01-31 18:09:30+08
  69719 |   61453 | uZuNbGXsWnXXRECH6PGeFdtf | COG_001_ELEARNING | 
16.html | 2002-01-31 18:07:49+08



The oid correctly reflects the order of the insertion of the rows... 
but look at the timestamp - the last row has a timestamp _2 minutes 
before_ the previous row. How could this be happening? We know row 
69719 was inserted _after_ 69718, by probably about 30 seconds.

Thanks much.

Elaine Lindelef

Thu, 31 Jan 2002 18:20:47 -0800
Elaine Lindelef <eel@cognitivity.com>
timestamp weirdness
...
> The oid correctly reflects the order of the insertion of the rows...
> but look at the timestamp - the last row has a timestamp _2 minutes
> before_ the previous row. How could this be happening? We know row
> 69719 was inserted _after_ 69718, by probably about 30 seconds.

The timestamp provided as a result of evaluating 'now' is the time of
the start of the transaction, not the instantaneous wall clock time (if
you want the latter there is a function to provide it).

So, the times will reflect the time the transaction was started, while
the OID will reflect the order in which the insert/update actually
happened within the transaction.

hth

                    - Thomas

Fri, 01 Feb 2002 04:14:02 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: timestamp weirdness
At 04:14 AM 01-02-2002 +0000, Thomas Lockhart wrote:
>...
>> The oid correctly reflects the order of the insertion of the rows...
>> but look at the timestamp - the last row has a timestamp _2 minutes
>> before_ the previous row. How could this be happening? We know row
>> 69719 was inserted _after_ 69718, by probably about 30 seconds.
>
>The timestamp provided as a result of evaluating 'now' is the time of
>the start of the transaction, not the instantaneous wall clock time (if
>you want the latter there is a function to provide it).
>
>So, the times will reflect the time the transaction was started, while
>the OID will reflect the order in which the insert/update actually
>happened within the transaction.

Do postgresql backends still preallocate ranges of OIDs?

Link.


Fri, 01 Feb 2002 13:43:44 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: timestamp weirdness
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Do postgresql backends still preallocate ranges of OIDs?

Good point ... but in 7.1 they don't anymore.  There's just one shared
OID counter.

			regards, tom lane

Fri, 01 Feb 2002 09:45:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: timestamp weirdness 
>...
> > The oid correctly reflects the order of the insertion of the rows...
> > but look at the timestamp - the last row has a timestamp _2 minutes
> > before_ the previous row. How could this be happening? We know row
> > 69719 was inserted _after_ 69718, by probably about 30 seconds.
>
>The timestamp provided as a result of evaluating 'now' is the time of
>the start of the transaction, not the instantaneous wall clock time (if
>you want the latter there is a function to provide it).
>
>So, the times will reflect the time the transaction was started, while
>the OID will reflect the order in which the insert/update actually
>happened within the transaction.
>
>hth
>
>                    - Thomas

The beginning of the transactions was definitely in the same order as 
the OID reflects, and I'm quite sure the previous transaction was 
completed before the next connection was started as well.

Elaine


Fri, 1 Feb 2002 09:37:58 -0800
Elaine Lindelef <eel@cognitivity.com>
Re: timestamp weirdness
> >The timestamp provided as a result of evaluating 'now' is the time of
> >the start of the transaction, not the instantaneous wall clock time (if
> >you want the latter there is a function to provide it).
> We are now thinking that what is happening is that the first write
> may have a long open transaction.
> What is the function you mention to provide instantaneous time?

lockhart=# select timestamp 'now', cast(timeofday() as timestamp);
        ?column?        |         ?column?          
------------------------+---------------------------
 2002-02-01 17:01:19-08 | 2002-02-01 17:01:31.21-08

hth

                     - Thomas

Fri, 01 Feb 2002 17:03:14 -0800
Thomas Lockhart <lockhart@fourpalms.org>
Re: [GENERAL] timestamp weirdness