undefined | Better HN

0 pointsjacques_chester12y ago0 comments

Snodgrass wrote an entire book about dealing with the temporal blindness of SQL which might help you:

http://www.cs.arizona.edu/people/rts/tdbbook.pdf

More generally, you can usually build a model that handles (2), the changing state, by generalising your original model. I've done in both of classical ways, having either validity period fields (thus pushing complexity into every query), or by having audit tables (thus relying on triggers).

But building a changing model is hard, because at the schema level, SQL only supports (1). That is, it provides primitives to change the current state of the model.

So you wind up having to build a meta-model. The deficiencies of SQL (and I'm an RDBMS bigot) mean that we wind up building inner platforms. Or outer platforms -- witness migration toolkits.

Yet support for changing models is essential. No model is ever constant. Most of my work is for a public-sector client whose legal reporting requirements are constantly changing. You cannot throw away old reports which were made under old regulations; you must be able to recreate the world at any point in time. Which requires either a lot of bookkeeping code or taking periodic snapshots.

Datomic looks neat. I see that I'm not the only one who made the leap from transactional memory to transactional models.

Edit: I just found notes I wrote in 2009 on database languages while looking for something else. Spooky: http://chester.id.au/2013/08/28/notes-towards-a-set-objectiv...

0 comments

twic12y ago

Databases which use MVCC handle (2) - and if they have transactional DDL, then they cover (3). But they don't keep the history permanently. Who wants all that old data?

The one exception to this that i know of is Oracle, which although not fashionable round here, does expose the MVCC log through "flashback queries", where one can simply write "SELECT * FROM USER AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)":

http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_...

I don't know what the logistics of keeping an infinite history are. I do know that an e-commerce system i worked with before kept a few days, and that that saved our bacon on a couple of occasions.

jacques_chesterOP12y ago

They have the technological underpinnings, but it's not properly exposed via SQL. In practice you wind up having to do it all by hand.

What the F1 guys seem to have done is said "well that's just silly, let's take advantage of it".

Oracle is my day job, I've been saved by a flashback query once. A DBA misunderstood a request I was making and applied a dev change to production.

> I don't know what the logistics of keeping an infinite history are.

I'd say they're not much different than asking a database developer to be able to generate reports for any given span of time. Or keeping a complete audit trail of changes. Or, more broadly, complete website logs, complete source revision history etc etc.

That is: in theory it creates an infinite downside. In practice it is more valuable than not, especially with the ever-plunging cost of storage.

dragonwriter12y ago

PostgreSQL used to allow this via "Time Travel", but it has long since been removed (I think because it was rarely used and supporting it interfered with other optimizations.)

j / k navigate · click thread line to collapse