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

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com