timestamp fields and order by? - Mailing list pgsql-general

It appears as though the timestamp resolution is now low
enough that it cannot keep up with the speed at which
items can be inserted.  That is, when ordering entries
by timestamp, it's possible that the ordering will not
reflect the actual entry order.  (I assume the corollary
is that the sort used for 'ORDER BY' isn't stable, right?)

Here's a sample illustrating what I see.  Entries were
inserted by a Java (really Jython) program that was a
simple countdown timer.  Selecting the fields without ordering
shows the counts as monotonically decreasing, as expected.
But ordering by the time_stamp column results in some
entries being rearranged.

Is my analysis correct?  Is there anything that can be
done about it?  I expect to be recording high-speed
data in the future that won't have a simple value
(the countdown number, in this example) that can be
used to obtain the original entry order.

Thanks for any advice!

-Steve

-------------------------------------------------------
atst.archivedb=# select version();
                                                     version
----------------------------------------------------------------------------------------------------------------
  PostgreSQL 8.4.2 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44), 32-bit
(1 row)

atst.archivedb=# \d archive
                 Table "public.archive"
    Column   |            Type             | Modifiers
------------+-----------------------------+-----------
  time_stamp | timestamp without time zone |
  source     | character varying(128)      |
  name       | character varying(128)      |
  value      | text                        |

atst.archivedb=# select time_stamp,value from archive limit 10;
        time_stamp        |                                                     value


-------------------------+---------------------------------------------------------------------------------------------------------------
  2010-01-06 19:02:04.509 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.467]),
(eventTest.eventTest: [1000])}
  2010-01-06 19:02:04.511 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.489]),
(eventTest.eventTest: [999])}
  2010-01-06 19:02:04.511 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.490]),
(eventTest.eventTest: [998])}
  2010-01-06 19:02:04.512 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.490]),
(eventTest.eventTest: [997])}
  2010-01-06 19:02:04.512 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.491]),
(eventTest.eventTest: [996])}
  2010-01-06 19:02:04.513 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.491]),
(eventTest.eventTest: [995])}
  2010-01-06 19:02:04.513 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.492]),
(eventTest.eventTest: [994])}
  2010-01-06 19:02:04.514 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.493]),
(eventTest.eventTest: [993])}
  2010-01-06 19:02:04.514 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.495]),
(eventTest.eventTest: [992])}
  2010-01-06 19:02:04.514 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.496]),
(eventTest.eventTest: [991])}
(10 rows)

atst.archivedb=# select time_stamp,value from archive order by time_stamp limit 10;
        time_stamp        |                                                     value


-------------------------+---------------------------------------------------------------------------------------------------------------
  2010-01-06 19:02:04.509 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.467]),
(eventTest.eventTest: [1000])}
  2010-01-06 19:02:04.511 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.489]),
(eventTest.eventTest: [999])}
  2010-01-06 19:02:04.511 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.490]),
(eventTest.eventTest: [998])}
  2010-01-06 19:02:04.512 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.490]),
(eventTest.eventTest: [997])}
  2010-01-06 19:02:04.512 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.491]),
(eventTest.eventTest: [996])}
  2010-01-06 19:02:04.513 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.492]),
(eventTest.eventTest: [994])}
  2010-01-06 19:02:04.513 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.491]),
(eventTest.eventTest: [995])}
  2010-01-06 19:02:04.514 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.496]),
(eventTest.eventTest: [991])}
  2010-01-06 19:02:04.514 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.495]),
(eventTest.eventTest: [992])}
  2010-01-06 19:02:04.514 | {(__.source: [jython script demo]), (__.timestamp: [2010-01-06 19:02:04.493]),
(eventTest.eventTest: [993])}
(10 rows)

--------------------------------------------------------
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.