Re: Repeatable read and serializable transactions see data committed after tx start

On 06/11/14 02:06, Jim Nasby wrote:
> On 11/5/14, 6:04 PM, Álvaro Hernández Tortosa wrote:
>>
>> On 05/11/14 17:46, Jim Nasby wrote:
>>> On 11/4/14, 6:11 PM, Álvaro Hernández Tortosa wrote:
>>>> Should we improve then the docs stating this more clearly? Any
>>>> objection to do this?
>>>
>>> If we go that route we should also mention that now() will no longer
>>> be doing what you probably hope it would (AFAIK it's driven by BEGIN
>>> and not the first snapshot).
>>
>> If I understand you correctly, you mean that if we add a note to
>> the documentation stating that the transaction really freezes when
>> you do the first query, people would expect now() to be also frozen
>> when the first query is done, which is not what happens (it's frozen
>> at tx start). Then, yes, you're right, probably *both* the isolation
>> levels and the now() function documentation should be patched to
>> become more precise.
>
> Bingo.
>
> Hrm, is there anything else that differs between the two?
>
>>> Perhaps we should change how now() works, but I'm worried about what
>>> that might do to existing applications...
>>
>> Perhaps, I also believe it might not be good for existing
>> applications, but it definitely has a different freeze behavior,
>> which seems inconsistent too.
>
> Yeah, I'd really rather fix it...

There has been two comments which seem to state that changing this
may introduce some performance problems and some limitations when you
need to take out some locks. I still believe, however, that current
behavior is confusing for the user. Sure, one option is to patch the
documentation, as I was suggesting.

But what about creating a flag to BEGIN and SET TRANSACTION
commands, called "IMMEDIATE FREEZE" (or something similar), which
applies only to REPEATABLE READ and SERIALIZABLE? If this flag is set
(and may be off by default, but of course the default may be
configurable via a guc parameter), freeze happens when it is present
(BEGIN or SET TRANSACTION) time. This would be a backwards-compatible
change, while would provide the option of freezing without the nasty
hack of having to do a "SELECT 1" prior to your real queries, and
everything will of course be well documented.

What do you think?

Best regards,

Álvaro

--
Álvaro Hernández Tortosa

-----------
8Kdata