Re: Question about behavior of snapshot too old feature

On Fri, Oct 14, 2016 at 1:40 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> Hi all,
>
> I have a question about behavior of snapshot too old feature.
>
> 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.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com