Re: Allow "snapshot too old" error, to prevent bloat - Mailing list pgsql-hackers

Greg Stark <stark@mit.edu> wrote:
> On Sun, Feb 15, 2015 at 8:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> 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.
>>
> But I think Kevin would agree with you there. That's why he's
> interested in having the errors not occur if you don't read from
> the volatile tables. Ie, your reporting query reading from
> read-only tables would be guaranteed to succeed even if some
> other table had had some rows vacuumed away.
>
> I'm not sure that's really going to work because of things like
> hint bit updates or hot pruning. That is, say your table is
> read-only now but last week some of the records were updated. You
> might reasonably expect those rows to be safe and indeed the rows
> themselves will still be in your report. But the earlier versions
> of the rows could still be sitting on some pages invisible to
> every query but not vacuumable quite yet and then suddenly they
> get vacuumed away and the LSN on the page gets bumped.

That's a very interesting point.  In the case that the reporting
tables are like that (versus being created right before the run,
for the report), it would argue for either very aggressive
autovacuum settings or an explicit vacuum on those tables before
starting the report.

> Fwiw I would strongly suggest that instead of having a number of
> transactions to have a time difference.

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!

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company