Re: Storing Snapshot Data

Hi John,

John Gibson schrieb:
> Hi, all.
>
> I have a table which is continually updated with the latest totals.  I
> would like to take snapshots of some of the data in that table and store
> it in a second table to run statistics on it later.  What might some
> ways of doing this be?
>
> Illustrative (I hope) example using fruit-qty-on-hand at a grocery store:
>
> Fruit_table   {constantly updated by other processes}
>
> CREATE TABLE "fruit_table" (
>    "fruit_name"        varchar(20),
>    "fruit_qty"    int4
>  );
>
>
> ***TABLE DATA***
> fruit name     fruit_qty
> apple              5
> orange            8
> pear                3
>
>
>
> monitor_table {stores snapshots of fruit table from time to time}
>
> CREATE TABLE "monitor_table" (
>    "monitor_time" timestamp,
>    "mon_apples_qty"    int4,
>    "mon_oranges_qty"    int4,
>    "mon_pears_qty"        int4
> );
>
>
> I got the following to timestamp a single row from the fruit_table and
> put the results into the monitor_table:
>
> insert into monitor_table(monitor_time, mon_apples_qty)
> select now(), fruit_table.fruit_qty
> where fruit_name = 'apple';
>
> Unfortunately, I am stuck on how to get all three into the monitor table
> with the same timestamp.  Since the times will be relatively long
> between snapshots some type of variables or functions could be used (I
> guess) to store the current time ( curr_time := now(); ) and then run
> the query three times with first an insert and then two updates using
> the variable time stamp on the updates to locate the record to update.

Id use a third table to just store the snapshot times and a sequence
number:

CREATE SEQUENCE monitor_snapshots_id_seq;

CREATE TABLE monitor_snapshots (
    monitor_time timestamp,
    monitor_id int8 DEFAULT nextval('monitor_snapshots_id_seq'::text)
NOT NULL
    );

and then use the following code to take your snapshots:

INSERT INTO monitor_snapshots (monitor_time) VALUES (now());
INSERT INTO monitor_table
     SELECT currval('monitor_snapshots_id_seq'::text) as monitor_id,
            fruit_name,
            fruit_qty
     FROM fruit_table;

Provided you modify your monitor_table
to have monitor_id, fruit_name (perhaps fruit_id is better here),
fruit_qty

If you got a table with fruit_id:fruit_name, you can always retrive
your information via LEFT OUTER JOIN, otherwise you would only get
the kind of fruits available in the given snapshot.

HTH
Tino Wildenhain