table versioning approach (not auditing)

From: Abelard Hoffman <abelardhoffman(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: table versioning approach (not auditing)
Date: 2014-09-29 02:00:32
Message-ID: CACEJHMhpcv-94R_95pZt4o=-rVfTUki4i79m+aBgnOiASh9pyg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi. I need to maintain a record of all changes to certain tables so assist
in viewing history and reverting changes when necessary (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 or insert is done.

My current implementation is based on the wiki trigger examples, using a
single table, and a json column to record the row changes (rather than
hstore). What I like about that, in particular, is I can have a "global,"
chronological view of all versioned 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., find all changes to "user"
record 849, along with any changes to their "articles," "photos," etc.).
All of the data is in the json column, of course, but it seems like a pain
to try and build a query on the json column that can fetch all those
relationships (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 versioning tables associated
with each real table? Or another approach?

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gavin Flower 2014-09-29 03:00:00 Re: table versioning approach (not auditing)
Previous Message Pavel Stehule 2014-09-28 19:44:32 Re: how to see "where" SQL is better than PLPGSQL