On 22/08/12 02:16, Kevin Grittner wrote:
Jeff Davis <pg...@j-davis.com> wrote:
On Mon, 2012-08-20 at 19:32 -0500, Kevin Grittner wrote:
Josh Berkus <j...@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
So if I understand correctly...
If there is a very long running transaction, say 1 hour, then all (or
just some? - depending) transactions that nominally start and finish
within that time, can not have definitive start times until the very
long running transaction finishes, even if they are successfully committed?
So if someone looks at the audit log they might not see all the
transactions they expect to see.
So, if I had an automatic query A which updated statistics based on on
transactions committed over the last 10 minutes, then many (all?)
transactions starting and successfully completing during the time of the
very long running transaction will never show up! Here I am envisioning
a query fired off every ten minutes looking for audit records with
timestamps within the previous ten minutes. However, if I ran a query B
looking at audit record numbers with in 10 minute intervals for a week,
but kicked off 24 hours after the week finished -- then I would see the
records I did not see in query A.
Hmm... if I am at all right, then probably best to have some suitably
worded 'government health warning' prominent in the documentation!
Cheers,
Gavin
