Building dynamic contents from database - Mailing list pgsql-general

Hi,

My problem is rather complex. I will try to explain it clearly (I hope !)

Sorry for my poor english.

I'm tracking change of state for entities (something than can be
monitored.)

The set of states are limited (state1 to state5.)

A table is storing the current state for each entities, and another
table is storing the history when state change. A function, taking
two arguments (name, state) is provided to take care of updating
both table automatically.

For example:

The table 'current_state':

name | state  | date
----------------------------------------
foo  | state2 | <date since this state>
bar  | state3 |   '     '          '
baz  | state2 |   '     '          '

The table 'history_state':

name | state  | from_date | to_date
------------------------------------
foo  | state2 | <date>    | <date>
foo  | state1 | <date>    | <date>
bar  | state4 | <date>    | <date>
...
foo  | state3 | <date>    | <date>

To get a complete history for a specific entity, I've to make an UNION
from the two tables (Is it a current practice to split history/current ?)

Now, I want to define periods of time where the state is "logically"
forced to 'state0' (where 'state0' mean something like "don't care".)

Here is the table, 'override_state':

name | from_date | to_date
---------------------------
foo  | <date>    | <date>
foo  | <date>    | <date>
baz  | <date>    | <date>
bar  | <date>    | <date>

Each entry give a date interval where state must be considered as
'state0' whatever the current state is. The period for a same entity
can overlap, and can eventually be infinite (by NULLing to_date.)

I'm able to create a view that show current state (from 'state0' to
'state5'), but I don't see how to build a more complex view to be able
for example to query history by taking this new information into
account..

I think I've 3 possibilities:

 - run a external process that connect to database at each date from
   override_state (at from_date and to_date), and update history_state
   and current_state accordingly to provide at all time the corrected
   information. The problem here is to rely to an external process (and
   probably need lock in case where update occur at the same time..)

 - don't use view, but only stored procedure and give access to table
   through "dynamic" table by joining the 3 tables with long
   computation. The problem is that I want the fastest access.. and
   this is not really the case of this method.

 - or run a stored procedure before each access to table history_state
   or current_state. The problem is that it is costly in time too.

Here is a complete example for a single entity 'foo':

 * Since 10:00, the state is 'state1'
 * At 11:00, the state change to 'state2'
 * At 12:00, the state change to 'state3'

In the meantime, the table 'override_state' has 3 entries for 'foo':

 * between 10:15 and 10:45
 * between 10:30 and 11:10
 * between 11:55 and 12:00

Thus, consulting the database at 11:30 should show the following:

current_state

name | state  | since
----------------------
foo  | state2 | 11:10

history_state

name | state  | from  |  to
------------------------------
foo  | state0 | 10:15 | 11:10
foo  | state1 | 10:00 | 10:15


And at 13:00:

current_state

name | state  | since
----------------------
foo  | state3 | 12:00

history_state

name | state  | from  |  to
------------------------------
foo  | state0 | 11:55 | 12:00
foo  | state1 | 11:10 | 11:55
foo  | state0 | 10:15 | 11:10
foo  | state1 | 10:00 | 10:15



Note: The current system (without this table 'override_state') give
1 millions of row for history, 5000 entries in current table, and 25
states information per second are fed to database (calling the
function described at start of this post.)

--
Frédéric Jolliton