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