Thread: timestamp fields and order by?

timestamp fields and order by?

From

Steve Wampler

Date:

06 January 2010, 19:33:28

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.

Re: timestamp fields and order by?

From

Tom Lane

Date:

06 January 2010, 19:46:54

Steve Wampler <swampler@noao.edu> writes:
> 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.

Your example looks like what's being called is current_timestamp(3),
or else something on the client side is rounding it off to 3 digits.
The bare function will give whatever resolution the operating system
supplies, down to microseconds at best (the limit of the POSIX API for
this).

Even so, though, I think it would be quite foolish to design an
application around the assumption that the timestamps of successive
insertions will be distinguishable.  Put in a serial column.

            regards, tom lane

Re: timestamp fields and order by?

From

Steve Wampler

Date:

06 January 2010, 19:57:32

Tom Lane wrote:
> Your example looks like what's being called is current_timestamp(3),
> or else something on the client side is rounding it off to 3 digits.
> The bare function will give whatever resolution the operating system
> supplies, down to microseconds at best (the limit of the POSIX API for
> this).

Ah - right.  I was assuming the client application was inserting using 'now()'
for the time_stamp field but reviewing the source shows it's building
the timestamp itself.  It's old code.

> Even so, though, I think it would be quite foolish to design an
> application around the assumption that the timestamps of successive
> insertions will be distinguishable.  Put in a serial column.

I'll do that.  I was a bit surprised to see that the sort wasn't
stable, however.  Was that intentional for performance, or just
not considered worth the effort?  (I know I'm better off with the
serial column in my case - just mildly curious).

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

Re: timestamp fields and order by?

From

Tom Lane

Date:

06 January 2010, 21:14:19

Steve Wampler <swampler@noao.edu> writes:
> Tom Lane wrote:
>> Even so, though, I think it would be quite foolish to design an
>> application around the assumption that the timestamps of successive
>> insertions will be distinguishable.  Put in a serial column.

> I'll do that.  I was a bit surprised to see that the sort wasn't
> stable, however.  Was that intentional for performance, or just
> not considered worth the effort?

I think our sort code isn't stable --- it's a quicksort which usually
isn't.  But in any case the physical tuple locations aren't guaranteed
to be strictly increasing, so changing the sort code wouldn't make it
safe.

            regards, tom lane