Re: Storing Snapshot Data

On Thursday 11 December 2003 12:42 am, John Gibson wrote:
> 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.
>
> That doesn't sound very elegant to me. Please help if you have any ideas.
>
> I am definately a newbie, so forgive me if this is trivial. Also, if
> another forum would be better for this, I would appreciate a nudge in
> that direction. :)
>
> ...john
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

First I would create a monitor table as follows

CREATE TABLE "fruit_table_moinitor" (
"fruit_name" varchar(20),
"fruit_qty" int4,
"t_stamp" timestamp
);

Then use the following transaction-

BEGIN;

INSERT INTO fruit_table_monitor(fruit_name,fruit_qty,t_stamp) SELECT
fruit_name,fruit_qty,now() from fruit_table;

COMMIT;

Calling the function now() inside a transaction locks the timestamp to the
time at the beginning of the transaction.
--
Adrian Klaver
aklaver(at)comcast(dot)net