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).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
