Re: snapshot too old, configured by time

From: Steve Singer <steve(at)ssinger(dot)info>
To: Kevin Grittner <kgrittn(at)ymail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: snapshot too old, configured by time
Date: 2015-09-09 02:02:29
Message-ID: BLU437-SMTP689E11A8D2D3FE1B46C286DC520@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


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/ 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:
> The Enterprise PostgreSQL Company

Attachment Content-Type Size application/x-perl 2.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2015-09-09 02:26:27 Re: Getting total and free disk space from paths in PGDATA
Previous Message Michael Paquier 2015-09-09 01:48:24 Re: Improving test coverage of extensions with pg_dump