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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
>> What this patch does is add a GUC call old_snapshot_threshold. It
>> defaults to -1, which leaves behavior matching unpatched code.
>> Above that it allows tuples to be vacuumed away after the number of
>> transaction IDs specified by the GUC have been consumed.
>
> TBH, I'm not sure why we'd wish to emulate Oracle's single worst
> operational feature.

I've run into cases where people have suffered horribly bloated
databases because of one ill-behaved connection. Some companies
don't want to be vulnerable to that and the disruption that
recovery from that bloat causes.

Note that the default is to maintain legacy PostgreSQL behavior;
the postgresql.conf file must be modified to see this error.

>> Unlike the other patch, this one is more at the "proof of concept"
>> phase, because it requires support in the heap and each index AM to
>> work correctly; so far I have only had time to cover the heap and
>> btree indexes.
>
> But, having said that, why would the index AMs care? Seems like what
> you are describing should be strictly a matter for VACUUM's removal
> rules. If we're going to have something as ugly as this, I would much
> rather it had a very small code footprint.

If a tuple is vacuumed away, you would not notice that if you were
running an index scan because you would not visit the page it used
to be on to see that the LSN had changed. We tried to sell the
idea that *any* scan using a snapshot past the threshold should
error out (which is the only way I can see to avoid making the
index AMs aware of this), but they insisted that there was no
reason to have scans fail when reading an unmodified table using an
old snapshot, or even an unmodified page of a modified table.

Due to the cost to this company of modifying their software to deal
with differrent behavior, they will not move without the behavior
I'm shooting for with this patch. Due to the value of this
customer to us, we will put this (or something to accomplish this
behavior) into our fork. We're happy to share it with the
community. If the community does not feel they need this behavior,
or wants to generate the error more aggressively to avoid touching
the index AMs, I understand.

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