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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
