Re: temporal support patch - Mailing list pgsql-hackers

Jeff Davis <pgsql@j-davis.com> wrote:
> On Mon, 2012-08-20 at 19:32 -0500, Kevin Grittner wrote:
>> 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).
> 
> There are all kinds of challenges here, and I'm glad you're
> thinking about them. I alluded to some problems here:
> 
>
http://archives.postgresql.org/message-id/1345415312.20987.56.camel@jdavis
> 
> But those might be a subset of the problems you're talking about.
> 
> It sounds like, at a high level, there are two problems:
> 
> 1. capturing the apparent order of execution in the audit log
> 2. assigning meaningful times to the changes that are consistent
> with the apparent order of execution
As far as I can see, transactions which execute DML at any
transaction isolation level other than serializable can be
considered to have occurred in commit order.  Transactions which
don't write to the database don't need to be considered as part of
the history, at least in terms of viewing prior state.  Same with
transactions which roll back.  (Now, failed transactions and reads
might be of interest for some audit reports, but that seems to me
like a different issue than a temporal database.)
The funny bit is for a serializable transaction (TN) which commits
after writing to the database -- you can't know the apparent order
of execution as long as there are any serializable transactions
active which can't see the work of TN (i.e., the transactions
overlap).  If such a transaction (TX) executes a read which
conflicts with a TN write, TX appears to have executed first, since
it doesn't see the work of TN, so I think the sequence number or
timestamp for TN has to follow that for TX even though TN committed
first.  On the other hand, TX might write something that conflicts
with a TN read, in which case TN will appear to have executed first
and must get a sequence number or timestamp before TX.
If there is a cycle, SSI will cancel one of the transactions
involved, so that can't occur anywhere in the time line.
So, if you want to allow serializable temporal queries, the timing
of a read-write serializable transaction can't be locked down until
all overlapping read-write serializable transactions complete; and
the apparent order of execution must be based on read-write
conflicts, which are tracked within SSI.  I think that if we can
generate a list of committed transactions in order based on this
logic, it could feed into replication system -- hot standby as well
as trigger-based systems.  I think we could generate snapshots which
exclude the transactions for which the order of execution has not
yet been determined, and avoid the delays involved in other possible
solutions.
There's a lot of detail missing here in terms of what the API would
be, and how we handle the summarization that can occur within SSI so
that it can continue to function within bounded memory even in
pessimal circumstances, but that's the general outline of my
concerns and suggested solution.
-Kevin