At 07:50 PM 1/29/2008, [EMAIL PROTECTED] wrote:
Date: Mon, 28 Jan 2008 20:16:35 -0800
From: Bryce Nesbitt <[EMAIL PROTECTED]>
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
