Re: Question about behavior of snapshot too old feature

On Fri, Oct 14, 2016 at 8:53 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Fri, Oct 14, 2016 at 1:40 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:

>> For example, I set old_snapshot_threshold = 1min and prepare a table
>> and two terminals.
>> And I did the followings steps.
>>
>> 1. [Terminal 1] Begin transaction and get snapshot data and wait.
>> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
>> SELECT * FROM test;
>>
>> 2. [Terminal 2] Another session updates test table in order to make
>> snapshot dirty.
>> BEGIN;
>> UPDATE test SET c = c + 100;
>> COMMIT;
>>
>> 3. [Terminal 1] 1 minute after, read the test table again in same
>> transaction opened at #1. I got no error.
>> SELECT * FROM test;
>>
>> 4. [Terminal 2] Another session reads the test table.
>> BEGIN;
>> SELECT * FROM test;
>> COMMIT;
>>
>> 5. [Terminal 1] 1 minute after, read the test table again, and got
>> "snapshot error" error.
>> SELECT * FROM test;
>>
>> Since #2 makes a snapshot I got at #1 dirty, I expected to get
>> "snapshot too old" error at #3 where I read test table again after
>> enough time. But I could never get "snapshot too old" error at #3.
>>
>
> Here, the basic idea is that till the time corresponding page is not
> pruned or table vacuuming hasn't triggered, this error won't occur.
> So, I think what is happening here that during step #4 or step #3, it
> has pruned the table, after which you started getting error.

The pruning might be one factor. Another possible issue is that
effectively it doesn't start timing that 1 minute until the clock
hits the start of the next minute (i.e., 0 seconds after the next
minute). The old_snapshot_threshold does not attempt to guarantee
that the snapshot too old error will happen at the earliest
opportunity, but that the error will *not* happen until the
snapshot is *at least* that old. Keep in mind that the expected
useful values for this parameter are from a small number of hours
to a day or two, depending on the workload. The emphasis was on
minimizing overhead, even when it meant the cleanup might not be
quite as "eager" as it could otherwise be.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company