Greetings,
I have a PostgreSQL-8.x database that contains several tables which
hold the results of automated regression tests. Each table contains a
specific class of test, however the results from different subtests of
that class of test reside in a single table. For example, I have
tests A, B & C stored in tables a, b & c, and then there are subtests
such as 'build a' and 'test a' in table a, etc.
Once each day, I run a report against the contents of these tables,
which then emails a report which lists only the subtests which have
failed in the previous 24 hours. For this, I'm doing a SQL query
along the lines of:
select subtest,os,arch,info FROM ${table} WHERE (SELECT now() -
interval '24 hours' < date_created::timestamp)='t' AND
current_status='FAILED' ;
The problem here is that occasionally the same subtest runs more than
once in a 24 hour period (although not often), and not all of the test
runs will have the same result (current_status). To make matters more
complicated when this happens, the additional runs will have a
current_status of PASSED, yet my automated report still grabs the
older FAILED test result, and this confuses the hell out of the people
reviewing the report, since its technically passed already.
My problem is that I can't think of a non-trivial way to adjust the
SQL query so that it will only capture the last time the subtest ran,
in the event that it ran multiple times during a 24 hour window.
There are different permutations of os, arch & info (actually about
400 in total), so explicitly iterating through all of them is going to
be an ugly and expensive hack that I really want to avoid.
If it helps any, each row in the assorted tables has a unique id
number that auto-increments at insertion, but beyond that there isn't
any other non-trivial way to differentiate one row from another as the
fields are an assortment of text, integer, boolean & char.
Does anyone have any good ideas? thanks in advance.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama@gmail.com
LlamaLand http://netllama.linux-sxs.org
