Postgres Pro
Downloads
Home   >   mailing lists

Thread: timestamp weirdness

timestamp weirdness

From
Elaine Lindelef
Date:
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

Re: timestamp weirdness

From
Thomas Lockhart
Date:
...
> 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

Re: timestamp weirdness

From
Lincoln Yeoh
Date:
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.

Re: timestamp weirdness

From
Tom Lane
Date:
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

Re: timestamp weirdness

From
Elaine Lindelef
Date:
>...
> > 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