Re: Proposed archival read only trigger on rows - prevent history modification

At 07:50 PM 1/29/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Mon, 28 Jan 2008 20:16:35 -0800
>From: Bryce Nesbitt <bryce1(at)obviously(dot)com>
>To: pgsql-sql(at)postgresql(dot)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