"Tardis" approach for history tables - Mailing list pgsql-general

From Iván Perdomo
Subject "Tardis" approach for history tables
Date
Msg-id 56EC3307.7040905@akvo.org
Whole thread Raw
List pgsql-general
Hi all,

I'm trying to follow Magnus Hagander's [1] approach for having a history
tables. Basically having a copy of the schema table in a `history`
schema, plus an extra time range column for the validity period of a row.

I made a basic setup [2] and the version logging part is working "as
expected". On each INSERT|UPDATE|DELETE my history table changes the
`_validrange`.

I intend to use this approach on a "usual" web application, where
operations (INSERT|UPDATE|DELETE) happens frequently but not in a high
volume (as in many cases reads are more frequent). Another difference is
that we're not going to use an ORM, just plain SQL via JDBC.

My question is what edge case I'm missing by commenting out part of the
checks when dealing with the UPDATE operation [3].

I have tested the code with a single client making a transaction and
updating the row more than one time, and I got the expected result. Only
the last operation gets reflected in the history table.

[1] http://www.hagander.net/talks/tardis_orm.pdf
[2] https://gist.github.com/iperdomo/74890685f3c76b0fc139
[3]
https://gist.github.com/iperdomo/74890685f3c76b0fc139#file-tardis-sql-L53-L58

Thanks for your support,

--
Iván


Attachment

pgsql-general by date:

Previous
From: Jeff Janes
Date:
Subject: Re: ON CONFLICT DO NOTHING RETURNING
Next
From: rob stone
Date:
Subject: Re: spurious /dev/shm related errors on insert