On Oct24, 2014, at 20:24 , Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Oct 24, 2014 at 2:12 PM, Florian Pflug <fgp@phlo.org> wrote:
>>> What about doing one scan using SnapshotAny and then testing each
>>> returned row for visibility under both relevant snapshots? See
>>> whether there is any tuple for which they disagree.
>>
>> See my other mail - testing whether the snapshots agree isn't enough,
>> you'd have to check whether there could have been *any* snapshot taken
>> between the two which would see a different result.
>
> Oh, hmm. I had thought what I was proposing was strong enough to
> handle that case, but now I see that it isn't. However, I'm not
> entirely sure that it's the RI code's job to prevent such cases, or at
> least not when the transaction isolation level is less than
> serializable.
Well, the responsibility was laid onto the RI code by the decision to
not do SIREAD locking for RI enforcement queries. Simply enabling SIREAD
locking without doing anything else is not a good solution, I think -
it will drive up the false-positive rate. And it would make workloads
consisting purely of serializable transactions pay the price for the
row-level locking done by the RI triggers, without getting anything in
return.
> Is there an argument that the anomaly that results is
> unacceptable at REPEATABLE READ?
I'm not aware of any case that is clearly unacceptable for REPEATABLE
READ, but neither am I convinced that no such case exists. REPEATABLE READ
mode is hard because there doesn't seem to be any formal definition of
what we do and do not guarantee. The guarantees provided by the SQL
standard seem to be so much weaker than what we offer that they're not
really helpful :-(
I believe the best way forward is to first find a solution for SERIALIZABLE
transactions, and then check if it can be applied to REPEATABLE READ
mode too. For SERIALIZABLE mode, it's at least clear what we're aiming
for -- offering true serializability.
best regards,
Florian Pflug