Re: On-the-fly index tuple deletion vs. hot_standby

On Tue, Jun 14, 2011 at 5:28 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Mon, Jun 13, 2011 at 04:16:06PM +0100, Simon Riggs wrote:
>> On Mon, Jun 13, 2011 at 3:11 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> > On Sun, Jun 12, 2011 at 3:01 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
>> >> Assuming that conclusion, I do think it's worth starting
>> >> with something simple, even if it means additional bloat on the master in the
>> >> wal_level=hot_standby + vacuum_defer_cleanup_age / hot_standby_feedback case.
>> >> In choosing those settings, the administrator has taken constructive steps to
>> >> accept master-side bloat in exchange for delaying recovery conflict. ?What's
>> >> your opinion?
>> >
>> > I'm pretty disinclined to go tinkering with 9.1 at this point, too.
>>
>> Not least because a feature already exists in 9.1 to cope with this
>> problem: hot standby feedback.
>
> A standby's receipt of an XLOG_BTREE_REUSE_PAGE record implies that the
> accompanying latestRemovedXid preceded or equaled the master's RecentXmin at the
> time of issue (see _bt_page_recyclable()).  Neither hot_standby_feedback nor
> vacuum_defer_cleanup_age affect RecentXmin.  Therefore, neither facility delays
> conflicts arising directly from B-tree page reuse.  See attached test script,
> which yields a snapshot conflict despite active hot_standby_feedback.

OK, agreed. Bug. Good catch, Noah.

Fix is to use RecentGlobalXmin for the cutoff when in Hot Standby
mode, so that it is under user control.

Attached patch will be applied to head and backpatched to 9.1 and 9.0
to fix this.

No effect on non-users of Hot Standby. Minimal invasive for HS users.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services