Re: History-based (or logged) database. - Mailing list pgsql-general
From | elein |
---|---|
Subject | Re: History-based (or logged) database. |
Date | |
Msg-id | 20040111194148.H12147@cookie.varlena.com Whole thread Raw |
In response to | Re: History-based (or logged) database. ("Chris Travers" <chris@travelamericas.com>) |
List | pgsql-general |
On Mon, Jan 05, 2004 at 05:16:23PM +0700, Chris Travers wrote: > Hi Octavio; > > I have had to do something like this (hotel reservation app I am developing) > and want to be able to reconstruct an accurate picture of the database from > any point in time for reporting purposes (suppose I change the configuration > of a room and want to see vacancy rate info for a certain configuration on a > certain date). > > Here is what I did. This is off the top of my head, and may not work as > written here, but it should at least show the idea and structure of my > solution ot the problem. > (simple excerpt that shows the example): > > create table rooms ( > room_id varchar(64) primary key, > class_id int4 references rclasses(class_id) > ); > > create table room_archive ( > room_id varchar(64) NOT NULL, > class_id int4 NOT NULL, > valid_until TIMESTAMP NOT NULL); > > CREATE FUNCTION archive_room_trig() RETURNS TRIGGER AS ' > BEGIN > INSERT INTO room_archive(room_id, class_id, valid_until) > VALUES (old.room_id, old.class_id, now()); > END; > ' LANGUAGE PLPGSQL; > > CREATE TRIGGER trig_archive_room > BEFORE INSERT OR UPDATE OR DELETE ON rooms > FOR EACH ROW archive_room_trig(); > > CREATE VIEW room_history AS > SELECT room_id, class_id, now() AS valid_until FROM rooms > UNION > SELECT room_id, class_id, valid_until FROM room_archive; > > Best Wishes, > Chris Travers > ----- Original Message ----- > From: "Octavio Alvarez" <alvarezp@alvarezp.ods.org> > To: <pgsql-general@postgresql.org> > Sent: Monday, January 05, 2004 10:43 AM > Subject: [GENERAL] History-based (or logged) database. > > > > > > Hi! I would like to implement a database which allows me to keep track of > > changes from users, but I don't know if there is any model already used > > for this. Let me show you what I mean. > > > > Say I have a table t_table1 with 2 columns plus a PK. Normally my table > > with some data would look like: > > > > t_table1 > > ------------------ > > PK | col1 | col2 > > ------------------ > > 1 | 3 | 4 > > 2 | 4 | 7 > > 3 | 6 | 9 > > ... and so on... > > > > If I make a change, I can't get the info about who made the change and > > when did he do it, I can't do a "what was the value on a certain > > date"-type query. > > > > An UPDATE col1 = 9 WHERE pk = 1; would make t_table1 look like: > > > > t_table1: > > ------------------ > > PK | col1 | col2 > > ------------------ > > 1 | 9 | 4 > > 2 | 4 | 7 > > 3 | 6 | 9 > > ... and so on... > > > > To solve my "who and when", and "what on a certain date" problem, I was > > thinking on a platform like the following: > > > > t_table1: > > ------------------- > > PK | col1 | col2 | record_date | who_created > > ------------------- > > 1 | 3 | 4 | 2003-03-03 11:30:10 a.m. | alvarezp > > 2 | 4 | 7 | 2003-03-03 11:30:10 a.m. | alvarezp > > 3 | 6 | 9 | 2003-03-04 11:30:10 a.m. | alvarezp > > ... and so on... > > > > Now, an UPDATE col1 = 9 WHERE pk = 1; (done on '2003-03-05 12:00:00 a.m.') > > by 'ggarcia' would make t_table1 look like: > > > > t_table1: > > -------------------- > > UID | PK | col1 | col2 | record_date | who_created > > -------------------- > > 1 | 1 | 3 | 4 | 2003-03-03 11:30:10 a.m. | alvarezp > > 2 | 2 | 4 | 7 | 2003-03-03 11:30:10 a.m. | alvarezp > > 3 | 3 | 6 | 9 | 2003-03-04 11:30:10 a.m. | alvarezp > > 4 | 1 | 9 | 4 | 2003-03-05 12:00:00 a.m. | ggarcia > > ... and so on... > > > > I would extend SQL to include a "WHEN" clause in SELECT statements. If > > omitted, the query should use only the last valid records, using only UID > > = {2, 3, 4}, which will make it completely transparent to not-yet-updated > > applications. > > > > Of course, may be a "deleted" column would be needed in order to DELETE > > from t_table1;" and still have the data available for the hypothetical > > "SELECT ... WHEN '2003-03-03 3:00:00 p. m.';" > > > > Has anyone implemented something similar in PGSQL? If so, how have you > > done it? > > > > Thanks in advance. > > > > -- > > Octavio Alvarez Piza. > > E-mail: alvarezp@alvarezp.ods.org > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly In the current issue of PostgreSQL General Bits #57 http://www.varlena.com/GeneralBits there is also a brief example of tracking updates with triggers. Be careful out there! I think Chris referenced OLD values instead of NEW values in his version of the BEFORE TRIGGER. elein ============================================================ elein@varlena.com Varlena, LLC www.varlena.com PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ ============================================================= I have always depended on the [QA] of strangers.
pgsql-general by date: