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
createa 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
Ineeded.
> 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
withthe 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
itis 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
theobjects 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
applicationgrows 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
allowin-place updates at the time (i think it does now).