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

Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> writes:
> On 2/15/15 10:36 AM, Tom Lane wrote:
>> I wonder if we couldn't achieve largely the same positive effects through
>> adding a simple transaction-level timeout option.

> A common use-case is long-running reports hitting relatively stable data
> in a database that also has tables with a high churn rate (ie: queue
> tables). In those scenarios your only options right now are to suffer
> huge amounts of bloat in the high-churn or not do your reporting. A
> simple transaction timeout only "solves" this by denying you reporting
> queries.

Agreed, but Kevin's proposal has exactly the same problem only worse,
because (a) the reporting transactions might or might not fail (per
Murphy, they'll fail consistently only when you're on deadline), and
(b) if they do fail, there's nothing you can do short of increasing the
slack db-wide.

> An idea that I've had on this would be some way to "lock down" the
> tables that a long-running transaction could access. That would allow
> vacuum to ignore any snapshots that transaction had for tables it wasn't
> accessing. That's something that would be deterministic.

There might be something in that, but again it's not much like this patch.
The key point I think we're both making is that nondeterministic failures
are bad, especially when you're talking about long-running, expensive-to-
retry transactions.

regards, tom lane