Re: table versioning approach (not auditing) - Mailing list pgsql-general

Hey
 
i've also tried to implement a database versioning using JSON to log changes in tables. Here it is:
https://github.com/fxku/audit
I've got two versioning tables, one storing information about all transactions that happened and one where i put the
JSONlogs of row changes of each table. I'm only logging old values and not complete rows. 
 
Then I got a function that recreates a database state at a given time into a separate schema - either to VIEWs, MVIEWs
orTABLES. This database state could then be indexed in order to work with it. You can also reset the production state
tothe recreated past state. 
 
Unfortunately I've got no time to further work on it at the moment + I have not done tests with many changes in the
databaseso I can't say if the recreation process scales well. On downside I've realised is that using the json_agg
functionhas limits when I've got binary data. It gets too long. So I'm really looking forward using JSONB. 

There are more plans in my mind. By having a Transaction_Log table it should be possible to revert only certain
transactions.I'm also thinking of parallel versioning, e.g. different users are all working with their version of the
databaseand commit their changes to the production state. As I've got a unique history ID for each table and each row,
Ishould be able to map the affected records. 

Have a look and tell me what you think of it.

Cheers
Felix
 

Gesendet: Montag, 29. September 2014 um 04:00 Uhr
Von: "Abelard Hoffman" <abelardhoffman@gmail.com>
An: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Betreff: [GENERAL] table versioning approach (not auditing)

Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes
whennecessary (customer service makes an incorrect edit, etc.). 
 
I have studied these two audit trigger examples:
https://wiki.postgresql.org/wiki/Audit_trigger
https://wiki.postgresql.org/wiki/Audit_trigger_91plus
 
I've also read about two other approaches to versioning:
1. maintain all versions in one table, with a flag to indicate which is the current version
2. have a separate versions table for each real table, and insert into the associated version table whenever an update
orinsert is done. 
 
My current implementation is based on the wiki trigger examples, using a single table, and a json column to record the
rowchanges (rather than hstore). What I like about that, in particular, is I can have a "global," chronological view of
allversioned changes very easily. 
 
But there are two types of queries I need to run.
1. Find all changes made by a specific user
2. Find all changes related to a specific record
 
#1 is simple to do. The versioning table has a user_id column of who made the change, so I can query on that.
 
#2 is more difficult. I may want to fetch all changes to a group of tables that are all related by foreign keys (e.g.,
findall changes to "user" record 849, along with any changes to their "articles," "photos," etc.). All of the data is
inthe json column, of course, but it seems like a pain to try and build a query on the json column that can fetch all
thoserelationships (and if I mess it up, I probably won't generate any errors, since the json is so free-form). 
 
So my question is, do you think using the json approach is wrong for this case? Does it seem better to have separate
versioningtables associated with each real table? Or another approach? 
 
Thanks