Josh Berkus <josh@agliodbs.com> wrote:
> This is sounding like a completely runaway spec on what should be
> a simple feature.
I hate to contribute to scope creep (or in this case scope screaming
down the tracks at full steam), but I've been watching this with a
queasy feeling about interaction with Serializable Snapshot
Isolation (SSI). Under SSI the apparent order of execution is not
always the transaction commit order, or the transaction start order.
So a temporal database would be vulnerable to seeing anomalies like
this one unless rw-conflicts (as tracked with predicate locks) are
considered:
http://wiki.postgresql.org/wiki/SSI#Deposit_Report
This raises something I talked vaguely about in Ottawa this year,
although it was pretty much at the hand-waving stage and I don't
know how well I got the idea across. I've been thinking about the
problems with all the various replication technologies being able to
present data consistent with serializable transactions, and have the
outlines of a technique I think might be more palatable to the
community that those previously discussed. Basically, it would
involve generating a list of committed XIDs in *apparent order of
execution*, and creating snapshots on the replicas based on that
instead of just the master's transaction commit order. I've been
trying to work through the details to the point where I can present
a coherent write-up on it.
I wouldn't want to hold up a feature like temporal queries on the
basis that it didn't immediately play nice with SSI, but it seems
like it would be a good thing if the view of the past wasn't too
strictly tied to transaction commit sequence; a little bit of
abstraction there might save a lot of pain in tying these features
together. Maybe something along the lines of a transaction
visibility sequence number, or *maybe* a timestamptz works as long
as that can be fudged to a time after the commit time for
transactions involved in rw-conflicts with concurrent transactions.
(I'm not sure microsecond resolution works for other, reasons, but
if it does...) I think either could work.
-Kevin