Weird query sort

From: "Jeffrey Melloy" <jmelloy(at)gmail(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Weird query sort
Date: 2008-12-27 22:02:37
Message-ID: 3d2ffcdd0812271402r57625fb9q683a67d78654f21f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table, queries, with a column value. There is a trigger on
this table that inserts into query_history for each update to value.
I'm trying to graph the query_history table, so I was using a custom
aggregate to turn it into an array:

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

This worked out pretty well. I was initially concerned that the order
was correct because the table was stored in the right order on the
disk, so I got a query something like this:

select queries.query_id,
array_accum(value) as current_values,
min(query_time) as min_time,
max(query_time) as max_time
from queries,
(select query_id, value, query_time
from query_history
order by query_time) hist
where queries.query_id = hist.query_id
and dirty = true
and query_time <= update_time
and query_time > update_time - '1 hour'::interval
group by queries.query_id

This works out, but I decided to switch to the last 16 values instead
of the last hour.

So I ended up with this:
select queries.query_id,
array_accum(value) as current_values,
null as previous_values,
min(query_time) as min_time,
max(query_time) as max_time
from queries,
(select query_id, value, query_time from (
select query_id, value, query_time
from query_history
order by query_time desc
limit 16) desc_hist
order by query_time desc) hist
where queries.query_id = hist.query_id
and dirty = true
and query_time <= update_time
group by queries.query_id

The part I'm wondering about is this piece:
(select query_id, value, query_time from (
select query_id, value, query_time
from query_history
order by query_time desc
limit 16) desc_hist
order by query_time desc) hist

I was intiially trying to sort the inner loop by the time descending,
and the outer loop by the time ascending, but that resulted in an
array that was the reverse of the desired order. Switching the outer
query to order by desc fixed it and comes out in the proper order.

It seems like I should be able to order by quer_time desc and then
query_time asc. Am I missing something? Is this a bug?

-Jeff

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-12-28 00:08:36 Re: Weird query sort
Previous Message Alvaro Herrera 2008-12-27 14:10:17 Re: Automatic CRL reload