Storing Snapshot Data

[John Gibson]

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_tabl e" (
"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_ta ble" (
"monitor_ti me" timestamp,
"mon_apples_qty " int4,
"mon_oranges_qt y" 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(m onitor_time, mon_apples_qty)
select now(), fruit_table.fru it_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

[Adrian Klaver]

Re: Storing Snapshot Data

On Thursday 11 December 2003 12:42 am, John Gibson wrote:[color=blue]
> 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_tabl e" (
> "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_ta ble" (
> "monitor_ti me" timestamp,
> "mon_apples_qty " int4,
> "mon_oranges_qt y" 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(m onitor_time, mon_apples_qty)
> select now(), fruit_table.fru it_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[/color]

First I would create a monitor table as follows

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

Then use the following transaction-

BEGIN;

INSERT INTO fruit_table_mon itor(fruit_name ,fruit_qty,t_st amp) SELECT
fruit_name,frui t_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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tino Wildenhain]

Re: Storing Snapshot Data

Hi John,

John Gibson schrieb:[color=blue]
> 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_tabl e" (
> "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_ta ble" (
> "monitor_ti me" timestamp,
> "mon_apples_qty " int4,
> "mon_oranges_qt y" 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(m onitor_time, mon_apples_qty)
> select now(), fruit_table.fru it_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.[/color]

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

CREATE SEQUENCE monitor_snapsho ts_id_seq;

CREATE TABLE monitor_snapsho ts (
monitor_time timestamp,
monitor_id int8 DEFAULT nextval('monito r_snapshots_id_ seq'::text)
NOT NULL
);

and then use the following code to take your snapshots:

INSERT INTO monitor_snapsho ts (monitor_time) VALUES (now());
INSERT INTO monitor_table
SELECT currval('monito r_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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Re: Storing Snapshot Data

John Gibson <gib@edgate.com > writes:[color=blue]
> Unfortunately, I am stuck on how to get all three into the monitor table
> with the same timestamp.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster