Re: snapshot too old, configured by time

On 08/31/2015 10:07 AM, Kevin Grittner wrote:

Kevin,

I've started to do a review on this patch but I am a bit confused with
some of what I am seeing.

The attached testcase fails I replace the cursor in your test case with
direct selects from the table.
I would have expected this to generate the snapshot too old error as
well but it doesn't.

# Failed test 'expect "snapshot too old" error'
# at t/002_snapshot_too_old_select.pl line 64.
# got: ''
# expected: '72000'
# Looks like you failed 1 test of 9.
Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/9 subtests

Am I misunderstanding something or is the patch not working as expected?

> As discussed when the "proof of concept" patch was submitted during
> 9.5 development, here is a version intended to be considered for
> commit to 9.6, with the following changes:
>
> 1. It is configured using time rather than number of transactions.
> Not only was there unanimous agreement here that this was better,
> but the EDB customer who had requested this feature and who had
> been testing it independently made the same request.
>
> 2. The "proof of concept" patch only supported heap and btree
> checking; this supports all index types.
>
> 3. Documentation has been added.
>
> 4. Tests have been added. They are currently somewhat minimal,
> since this is using a whole new technique for testing from any
> existing committed tests -- I wanted to make sure that this
> approach to testing was OK with everyone before expanding it. If
> it is, I assume we will want to move some of the more generic
> portions to a .pm file to make it available for other tests.
>
> Basically, this patch aims to limit bloat when there are snapshots
> that are kept registered for prolonged periods. The immediate
> reason for this is a customer application that keeps read-only
> cursors against fairly static data open for prolonged periods, and
> automatically fields SQLSTATE 72000 to re-open them if necessary.
> When used, it should also provide some protections against extreme
> bloat from forgotten "idle in transaction" connections which are
> left holding a snapshot.
>
> Once a snapshot reaches the age threshold, it can be terminated if
> reads data modified after the snapshot was built. It is expected
> that useful ranges will normally be somewhere from a few hours to a
> few days.
>
> By default old_snapshot_threshold is set to -1, which disables the
> new behavior.
>
> The customer has been testing a preliminary version of this
> time-based patch for several weeks, and is happy with the results
> -- it is preventing bloat for them and not generating "snapshot too
> old" errors at unexpected times.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>