Re: Proposed archival read only trigger on rows - prevent history modification - Mailing list pgsql-sql

At 07:50 PM 1/29/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Mon, 28 Jan 2008 20:16:35 -0800
>From: Bryce Nesbitt <bryce1@obviously.com>
>To: pgsql-sql@postgresql.org
>Subject: Proposed archival read only trigger on rows - prevent history 
>modification
>[snip]
>I'm considering building a protective mechanism, and am seeking 
>feedback
>on the idea.  The approach would be to add a new column named "ro" to
>each table at invoice level and below.  Then have a trigger on
>'ro'==true deny the write, and probably raise a huge stink.  As 
>invoice
>are mailed each month, all the supporting data would be set to "ro" 
>true.
>[snip]

Hi Bryce,

I have a similar situation but a little in reverse. I have many sets of 
*incoming* records, which I want to preserve, though at any one time 
there is only one "live" version of the incoming records. Sometimes I 
have to read and compare versions of the records, live or otherwise. 
The logical records I'm talking about occupy a number of tables that 
are joined together in the database itself.

My solution, which required a little middleware engineering, was to 
create two tables for each table that had multiple "versions" of 
records. So, I have  a "property" table and a "property_versions" table 
that have identical table structures. I have a column common to every 
such versioned set of tables called "import_group_id." For live tables, 
this just tells me which version from the "*_versions" table is 
currently being used for that row. The live tables have primary keys 
just like normal ("id" as a serial int field). The "versions" tables' 
primary keys are different, compound keyed off "id" and 
"import_group_id." This permits normalization but also allows multiple 
versions of the same records.

In your case, I'd say you could archive your data table to a 
"data_versions" table. You might archive periodically and leave the 
records on the live table (but knowing that audit versions are safely 
tucked away and easily accessible/comparable), or you might migrate the 
records off the live table onto the versions table (insert followed by 
a delete in a transaction). If you adopted the latter method, you could 
union the two tables to get a complete set of rows. (Via a view even? 
Not sure if you can create a view on a union but it seems likely you 
can..)

You could also choose (like I did) to store multiple versions of the 
records, if your data are slowly changing rather than completely 
unchanging. However it sounds like your situation is such that you want 
to ensure old records are not modified ever. If you set up triggers 
and/or permission on the "data_versions" table, no one can ever delete 
or modify anything there. This might work perfectly for your purposes. 
Data in the live table can be edited as you like but changes to the 
"data_versions" table is simply not permitted. Inserts are allowed to 
data_versions but nothing else..

I hope this idea is useful. If I haven't explained it well, drop me a 
line and I'll try to clarify. Good luck with the project!

Steve