timestamp fields and order by?
От | Steve Wampler |
---|---|
Тема | timestamp fields and order by? |
Дата | |
Msg-id | 4B44E57B.2070207@noao.edu обсуждение исходный текст |
Ответы |
Re: timestamp fields and order by?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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.
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: FM format modifier does not remove leading zero from year