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

On Mon, Mar 14, 2011 at 01:56:22PM +0200, Heikki Linnakangas wrote:
> On 12.03.2011 12:40, Noah Misch wrote:
>> The installation that inspired my original report recently upgraded from 9.0.1
>> to 9.0.3, and your fix did significantly decrease its conflict frequency.  The
>> last several conflicts I have captured involve XLOG_BTREE_REUSE_PAGE records.
>> (FWIW, the index has generally been pg_attribute_relid_attnam_index.)  I've
>> attached a test script demonstrating the behavior.  _bt_page_recyclable approves
>> any page deleted no more recently than RecentXmin, because we need only ensure
>> that every ongoing scan has witnessed the page as dead.  For the hot standby
>> case, we need to account for possibly-ongoing standby transactions.  Using
>> RecentGlobalXmin covers that, albeit with some pessimism: we really only need
>> LEAST(RecentXmin, PGPROC->xmin of walsender_1, .., PGPROC->xmin of walsender_N)
>> - vacuum_defer_cleanup_age.  Not sure the accounting to achieve that would pay
>> off, though.  Thoughts?
>
> Hmm, instead of bloating the master, I wonder if we could detect more  
> accurately if there are any on-going scans, in the standby. For example,  
> you must hold a lock on the index to scan it, so only transactions  
> holding the lock need to be checked for conflict.

That would be nice.  Do you have an outline of an implementation in mind?