On Fri, Apr 22, 2011 at 11:10 AM, Noah Misch <n...@leadboat.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
