Re: Allow "snapshot too old" error, to prevent bloat

On Thu, Feb 19, 2015 at 6:01 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> I can see how they would be, provided we can be confident that we're
>> going to actually throw an error when the snapshot is out of date and
>> not end up returning incorrect results. We need to be darn sure of
>> that, both now and in a few years from now when many of us may have
>> forgotten about this knob.. ;)
>
> I get that this is not zero-cost to maintain, and that it might not
> be of interest to the community largely for that reason. If you
> have any ideas about how to improve that, I'm all ears. But do
> consider the actual scope of what was needed for the btree coverage
> (above). (Note that the index-only scan issue doesn't look like
> anything AM-specific; if something is needed for that it would be
> in the pruning/vacuum area.)

If I understood the issue correctly, you have long running snapshots
accessing one part of the data, while you have high churn on a
disjoint part of data. We would need to enable vacuum on the high
churn data while still being able to run those long queries. The
"snapshot too old" solution limits the maximum age of global xmin at
the cost of having to abort transactions that are older than this and
happen to touch a page that was modified after they were started.

What about having the long running snapshots declare their working
set, and then only take them into account for global xmin for
relations that are in the working set? Like a SET TRANSACTION WORKING
SET command. This way the error is deterministic, vacuum on the high
churn tables doesn't have to wait for the old transaction delay to
expire and we avoid a hard to tune GUC (what do I need to set
old_snapshot_threshold to, to reduce bloat while not having "normal"
transactions abort).

Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de