How to Implement Versioned Rows?
From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Mon, 06 Jan 2003 19:11:05 GMT
Message-ID: <dxkS9.139914$pe.5672581_at_news2.east.cox.net>
My application is gathering personel and patient data for a hospice. It must keep track of all changes to patient stats, chart, and med sheet over time. Therefore, I would like to keep versions of the rows in many of my tables.
)
\g
\g
\g
and deleted = 0
\g
-- Time travel view.
create view person_as_of as
select *
Date: Mon, 06 Jan 2003 19:11:05 GMT
Message-ID: <dxkS9.139914$pe.5672581_at_news2.east.cox.net>
My application is gathering personel and patient data for a hospice. It must keep track of all changes to patient stats, chart, and med sheet over time. Therefore, I would like to keep versions of the rows in many of my tables.
create table firm ( -- firm => hospice, clinic, hospital firm_id integer not null, name varchar(32), -- Just one example data column primary key (firm_id)
)
\g
create table transaction (
firm_id integer not null, transaction_id integer not null, -- Fed by sequence, one for each firm so -- we can part and merge databases by -- firm without collision! modified timestamp not null, modified_by person_id not null, primary key (firm_id, transaction_id), foreign key (firm_id) references firm)
\g
create table person_history ( -- Base for patient and employee firm_id integer not null, person_id integer not null, transaction_id integer not null, first_name varchar(32), -- Just two example data columns last_name varchar(32) not null, deleted boolean not null, primary key (firm_id, person_id, transaction_id))
\g
create view person as
select *
from person_history
where transaction_id = ( -- In explain this subselect appears to use index!
select max(transaction_id) from person_history as ph1 where firm_id = ph1.firm_id and person_id = ph1.firm_id )
and deleted = 0
\g
-- Time travel view.
create view person_as_of as
select *
In my application I can travel in time thus:
create temporary table past_transaction as
select transaction_id
from transaction
where modified <= '2002/12/2 17:59:00' -- the minute I turned 31
order by firm_id desc, transaction_id desc,
limit 1
\g
\g
Thoughts:
- I can write a query and have it travel back in time by setting one variable. Neeto.
- No archive tables or such means no copying, new version is a simple insert.
Good.
- With expliain the sub selects appear to use the indexes with aggregates, and if not I can alsways sort descending limit 1. Good.
- Even with complex joins on the latest view tables the query plans use the primary index for the sub select. Good.
- There is little need for vacuuming, since no updates are made to the busy tables of the application. Does this matter?
- Referenital integrity goes away from what I can see, since it won't understand the deleted column. Pity.
Questions:
- Is this viable or overly clever?
- Should I have a boolean latest column on a versioned table? This would mean update and vacuum, but potentially a faster query.
- Is there a penalty for long (how do you say?) concatenated keys in PostgreSQL?
- Any reason why this won't work with the coming distrubuted PostgreSQL?
- Where can I read about alternative implemenations for history/versioning? Nuances?
Thank you all for any input whatsoever.
Alan Gutierrez
Received on Mon Jan 06 2003 - 20:11:05 CET