Skip site navigation (1) Skip section navigation (2)

Building dynamic contents from database

From: Frederic Jolliton <fred-pg(at)jolliton(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Building dynamic contents from database
Date: 2003-12-29 20:03:11
Message-ID: 867k0f1jeo.fsf@mau.localdomain (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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

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

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:


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


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

And at 13:00:


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


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

pgsql-general by date

Next:From: Keith C. PerryDate: 2003-12-29 20:03:13
Subject: Re: Is my MySQL Gaining ?
Previous:From: Keith C. PerryDate: 2003-12-29 20:00:13
Subject: Re: Is my MySQL Gaining ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group