Thread: Building dynamic contents from database

Building dynamic contents from database

From
Frederic Jolliton
Date:
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


Re: Building dynamic contents from database

From
Scott Ribe
Date:
> name | state  | from_date | to_date
> ------------------------------------
> foo  | state2 | <date>    | <date>
> foo  | state1 | <date>    | <date>
> bar  | state4 | <date>    | <date>
> ...
> foo  | state3 | <date>    | <date>

I would consider using only 1 table, where a NULL to_date indicates the
current state, and you have a compound index on (name, to_date) to support
querying for current state. Perhaps you could use a partial index as well to
speed up those queries, but I haven't yet explored that feature of
PostgreSQL.



--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice


Re: Building dynamic contents from database

From
Frederic Jolliton
Date:
Scott Ribe <scott_ribe@killerbytes.com> writes:

>> name | state  | from_date | to_date
>> ------------------------------------
>> foo  | state2 | <date>    | <date>
>> foo  | state1 | <date>    | <date>
>> bar  | state4 | <date>    | <date>
>> ...
>> foo  | state3 | <date>    | <date>
>
> I would consider using only 1 table, where a NULL to_date indicates the
> current state, and you have a compound index on (name, to_date) to support
> querying for current state. Perhaps you could use a partial index as well to
> speed up those queries, but I haven't yet explored that feature of
> PostgreSQL.

Well.. There is already several indexes (choosed accordingly to
performance test). And, also, table current contains some fields not
found in history, so only 1 table for both type of information is not
ideal.

--
Frédéric Jolliton