Comment by josephg | Hacker News Reader

Comment by josephg | Hacker News Reader

Very cool. For a long time I've wanted databases to support this so I could run query 1:

`SELECT (, $querytime) FROM table1 WHERE ...;`

Then based on some code in my application run a second query:

`SELECT FROM table2 WHERE ... AT $querytime;`

So much frontend code makes assumptions that databases aren't modified between consecutive queries (like graphql-js). With an API like this it would be trivial to make those queries correct. (And we wouldn't need to abort, and we can cross database instance boundaries in interesting ways). The timestamp is also super useful for doing isomorphic rendering of live-bound data - the server can send the timestamp of the data queries it used to render the page. When the client JS loads it can reconnect to the server and pass the rendered timestamp back to check for deltas between the rendered version and the current database view. This in turn lets you to safely cache server renders, as well as a bunch of other fun things.

Hats off to the CockroachDB team! Fingers crossed some of these features start making their way into other databases I love. (Looking at you, Rethinkdb!)

That's essentially the working model of Datomic. In all other Database systems, you open "a database connection" that directly reads from live state and writes to live state (giving or taking MVCC.) With Datomic, you instead retrieve a handle to a snapshot of the database state at a given moment (which can be the present moment, or any previous moment) for reading.

For writing, you compute a change-set against a particular snapshot (making explicit the fact that you're always updating based on assumptions from possibly-stale data) and then submit that changeset to a write-linearizing "transactor" server-process that outputs new snapshots. The transactor is programmable, so it can do whatever interesting things you like to coalesce updates based on their submission order and the snapshot they were calculated against (CRDTs, last-write-wins using the snapshot-ordering, etc.)

Very cool. For a long time I've wanted databases to support this so I could run query

This has been in Oracle for a few years now https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns...

Yup, but it's one of those things where you need the most expensive license, IIRC.

I'm a little bemused by I’m not aware of any database that has implemented it

You would need to be hiding under a rock, like a cockroach I suppose, not to know that Oracle and SQL Server 2016 have this :-)

I think we've reached a point where a lot of OSS folks never actually touched a commercial database in their life. OSS alternatives are so good that less and less people feel the need to go out and pay for something better.

In a way it's similar to some folks going crazy because you can do this or that in the browser when desktop OSes have been able to do it for ages... generational shifts.

Certainly modern open source DBs are very capable... It is just a little frustrating to see the wheel being reinvented rather than genuine progress being made. And the big commercial DBs aren't standing still either.

I think CockroachDB is making genuine progress in the distributed, consistent space. We now have to add many of the existing features on top of that.

I searched for the syntax listed in the standard and didn't find it in production use anywhere else. I've used MSSQL in production before (last time in 2015) but never Oracle. I'm happy to now know that other SQL DBs have this feature.

Couldn't you do it with transaction isolation levels? for example in Postgres "BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;"

Inside the transaction you will see only changes made within the transaction.

Yes, but you would need to keep a transaction open for each user session.

I think the stateless nature of time-travelling queries would map much better to the stateless nature of HTTP requests.

Reply on news.ycombinator.com