Re: old_snapshot_threshold's interaction with hash index

On Tue, May 3, 2016 at 12:17 PM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:
> On Tue, May 3, 2016 at 11:09 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, May 3, 2016 at 11:46 AM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:
>>>> Uh, I have no idea how this would be fixed if the PageLSN is zero. Do
>>>> you?
>>>
>>> Yes, I see three ways, the most obvious of which is what Amit
>>> suggested -- don't do early vacuum on a table which has a hash index.
>>
>> What do you mean by "early VACUUM"?
>
> Both vacuuming and hot-pruning adjust xmin based on calling
> TransactionIdLimitedForOldSnapshots(TransactionId recentXmin,
> Relation relation). I'm talking about having that function, if all
> other conditions for the override pass, checking for a hash index,
> too.
>
>> Amit suggested disabling
>> HOT-pruning, but HOT-pruning happens completely outside of VACUUM. It
>> also happens inside VACUUM, so if we disabled HOT pruning, how could
>> we VACUUM at all? Sorry, I am confused.
>
> I guess we were both talking a bit loosely since (as I mentioned
> above) the function that adjusts the xmin is called for a vacuum or
> pruning. He mentioned one and I mentioned the other, but it's all
> controlled by TransactionIdLimitedForOldSnapshots().

OK, I see now: the basic idea here is that we can't prune based on the
newer XID unless the page LSN is guaranteed to advance whenever data
is removed. Currently, we attempt to limit bloat in non-unlogged,
non-catalog tables. You're saying we can instead attempt to limit
bloat only in non-unlogged, non-catalog tables without hash indexes,
and that will fix this issue. Am I right?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company