Обсуждение: Re: Proposed archival read only trigger on rows - prevent history modification
Re: Proposed archival read only trigger on rows - prevent history modification
От
Steve Midgley
Дата:
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