Re: table versioning approach (not auditing)

On Sep 29, 2014, at 4:06 PM, Nick Guenther wrote:

> A newbie tangent question: how do you access the transaction serial? Is it txid_current() as listed in http://www.postgresql.org/docs/9.3/static/functions-info.html?

My implementations were ridiculously simple/naive in design, and existed entirely with under defined serials. i'd just create a new record + id on a write operation, and then use it when logging all operations.

I had read up on a lot of (possibly better) ways to handle this using pg internals. They all seemed more advanced than I needed.

> And does your implementation worry about multiple timelines?

Not sure I understand this... but every object is given a revision id. edits between consecutive revisions are allowed, edits spanning multiple revisions are rejected.

On Sep 29, 2014, at 5:25 PM, Abelard Hoffman wrote:

> Felix & Jonathan: both of you mention just storing deltas. But if you do that, how do you associate the delta record with the original row? Where's the PK stored, if it wasn't part of the delta?

The logic I decided on, is this:

Revision 0
Only the original record is stored
Revision 1
• Copy the original record into revision store
Revision 1+
• Update the original record, store the deltas in the revision store

The reason why I chose this path, is that in my system:
• most records are not edited
• the records that are edited, are heavily edited

We use an ORM and it was simple to implement this pattern with it, and then write some functions in postgres to ensure it is adhered to.

When I need to pull data out:

• I can pull exact revisions out of the htstore for a given table/row using the revision ids as a key
• the revisions all contain the transaction id
• if i need to get more info about a given transaction, i can query the transactions table and get a list of all the objects that were edited within that transaction

if i wanted to ensure referential integrity, i could have used a table instead of an hstore (or json). If the application grows much larger, it will probably be migrated to a model like that. This approach just gave a lot of flexibility , minimized tables in the database, and was very easy to pull off. i went with hstore because json didn't allow in-place updates at the time (i think it does now).