Thread: Storing Snapshot Data
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
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@comcast.net
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
John Gibson <gib@edgate.com> writes:
> Unfortunately, I am stuck on how to get all three into the monitor table
> with the same timestamp.
Do all the work in a single SERIALIZABLE transaction. That gives you a
static, consistent view of the database for as long as you need.
regards, tom lane