By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

Building dynamic contents from database

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> 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
sc********@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

P: n/a
Scott Ribe <sc********@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.