Search Postgresql Archives

Weird query sort

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux