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

On Fri, Apr 22, 2011 at 11:10 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Tue, Mar 15, 2011 at 10:22:59PM -0400, Noah Misch wrote:
>> 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?
>
> In an attempt to resuscitate this thread, here's my own shot at that.  Apologies
> in advance if it's just an already-burning straw man.
>
> I didn't see any way to take advantage of checking for the heavyweight lock that
> any index scan would need to hold.

Have you looked at the logic in ResolveRecoveryConflictWithLock(), and
at GetLockConflicts()?

I am a little fuzzy on how the btree stuff works, but it seems to me
that you are looking for transactions that both have an xmin before
some threshold and also hold an AccessShareLock on some relation.
GetLockConflicts() will provide the latter, at least.

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