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

On 02/19/2015 09:44 AM, Kevin Grittner wrote:

> On the 15th I said this:
>
> | What this discussion has made me reconsider is the metric for
> | considering a transaction "too old". The number of transaction IDs
> | consumed seems inferior as the unit of measure for that to LSN or
> | time.
> |
> | It looks to me to be pretty trivial (on the order of maybe 30 lines
> | of code) to specify this GUC in minutes rather than transaction
> | IDs. At first glance this seems like it would be vulnerable to the
> | usual complaints about mismanaged clocks, but those are easily
> | answered by using a cached time in shared memory that we populate
> | in such a way that it can never move backward. Done right, this
> | could even allow the GUC to be changeable on reload rather than
> | only at restart. A badly mismanaged system clock could not cause a
> | query to generate incorrect results; the worst that could happen is
> | that this feature would fail to control bloat as much as expected
> | or reads of modified data could generate the "snapshot too old"
> | error around the time of the clock adjustment.
> |
> | As before, this would default to a magic value to mean that you
> | want the historical PostgreSQL behavior.
> |
> | If that makes the idea more palatable to anyone, I can submit a
> | patch to that effect within the next 24 hours.
>
> Until yesterday I didn't get any feedback suggesting that such a
> change would make the patch more palatable. Now that I have had,
> I'll try to post a patch to that effect today.
>
> Thanks!
>

I understand why this make people nervous. I wonder if it might be more
palatable if there were a per-table setting that could enable it? If we
could ensure that this was only applied to high churn queue tables, say,
while tables touched by the report writer would not have it applied,
that would calm a lot of my fears.

I'm also interested in handling the case Stephen Frost described, where
a tuple is effectively dead but we don't currently have the means of
discovering the fact, because there is an older long running transaction
which is never in fact going to be able to see the tuple.

cheers

andrew