[saurik]

Given that I need to share that common basis among the different parts of my system that need the consistent view, I am already taking the hit on that coupling: I am going to need to make certain that all of those parts all have access to the shared basis, whether it be a timestamp, a transaction identifier, or an active connection.

If the concern is coupling across space, you can store the transaction on the server in most existing databases. Past that, I would be highly interested in knowing the motivating use case that is causing the need for this much global and distributed snapshot isolation, but otherwise agree: I'd love to see more databases actually expose the ability to more easily "query the past" as well as request guarantees on vacuum avoidance.

[richhickey]

I would characterize the level of coupling involved in sharing "the basis is 12345" as categorically different from having to nest database access within the same transaction. Consider, e.g. the ease of moving the former to different processes.

[jeltz]

Agreed, but this is due to MVCC databases not saving the snapshots of committed transactions. If you have an active transaction in PostgreSQL you can start a new transaction using the same snapshot from another process. (This feature is exposed in SQL in version 9.2, and was motivated by the plan to implement parallel pg_dump.)

EDIT: You can find documentation of this at http://www.postgresql.org/docs/9.2/static/sql-set-transactio...

If you in PostgreSQL would save all historical snapshots and disable vacuum you could communicate any point in time simply with a snapshot number. Now this probably wont be very efficient since PostgreSQL is not optimized for this kind of use.

[saurik]

This feature is very interesting! However, it is definitely not designed for this purpose, and is thereby fairly heavyweight: it is writing a file to disk with information about the snapshot instead of just returning it to the client, and the result is the filename.

Not knowing this existed, I spent the last hour implementing this feature in a way that just requires getting a single integer out (the txid) and then restoring it as the snapshot being viewed (but not changing the txid of the running transaction, which solves a lot of the "omg what would that mean" problems).

http://news.ycombinator.com/item?id=4448767

With this implementation, you can just use txid_current() to save a transaction snapshot and you can restore it using my new variable (which correctly installs this functionality only into the currently executing transaction's snapshot). (In a more ideal world, I'd re-parse the string from txid_current_snapshot().)

I didn't also solve the vacuum problems, but I think there might be some reasonable ways to do that. Regardless, I imagine that most of the interesting usages of this are not "restore a snapshot from three days ago" but more "share a snapshot between multiple processes and machines for a few seconds".

However, if you can get even one of those processes to hold open a transaction, you can copy its snapshot to other transactions in other sessions, and then even this naive implementation should be guaranteed to have access to the old data.

If you are using it only for these short periods of time, for purposes of "distributed and decoupled consistency", we also don't need to worry about the overhead of never running a vacuum: the vacuum process runs as normal, as we are only holding on to data very temporarily.

That said, in practice, you really can go for quite a while without running a vacuum on your database without issues, and I imagine any alternative system is going to run into similar problems anyway (log structured merge trees, for example, get screwed on this after a while, as the bloom filters become less selective).

You can't, however, with PostgreSQL, make this work "forever" (if you wanted to store data going back until the beginning of time) due to 32-bit txid wraparound problems :(. (This also should affect my silly implementation, as I should save the epoch in addition to the txid.)