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@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.

This is my stab at a PostgreSQL implementation. I would greatly appreciate any input, criticisms, dire warnings, etc.

I plan on adding a transaction table with a transaction_id column fed by a sequence. The transaction_id will indicate the order of creation as well as time of creation.

• A row for each of our customers who subscribe to the application.

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

• A table to keep row of a transaction.

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

• Example versioned table.

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

• Show latest row view.

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 *

   from person_history
  where transction_id = (

            select max(transaction_id)
              from person_history as ph1
             where firm_id = ph1.firm_id
               and person_id = ph1.firm_id
               and transaction_id <= (
                       select transaction_id
                         from past_transaction
                        limit 1
                   )
        )

    and deleted = 0
\g

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
-- If only I could pass the view a parameter! select * from person_as_of
\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 - 13:11:05 CST