tom lane wrote: > "Curtis Faith" <[EMAIL PROTECTED]> writes: > > I don't dispute their conclusions in that context and under the > > circumstances they outline of random distribution of deletion and > > insertion values for the index keys. [But the random-distribution > > assumption doesn't always hold.] > > That's a fair point. Nonetheless, we have little choice: we > cannot move keys around during concurrent operations. If we > push keys to the right, we may cause an indexscan moving left > to miss them, and vice versa. So we can only eliminate empty pages. > > We could possibly allow VACUUM FULL to collapse partly-full > pages, since in that case we know there are no concurrent scans.
Couldn't we do an exclusive lock call on both leaf pages and the parent using a new LockBuffer type function, named something like LockBufferNoWait, that uses LWLockConditionalAcquire instead of LWLockAcquire, in the event that all three exclusive locks cannot be obtained release all three locks, sleep, and try again for N retries. (Actually, this would probably be four locks since the sibling pointer of one of the siblings would have to be updated to point to the new merged page instead of the to-be-deleted page.) Having exclusive locks on all three pages prior to a merge would ensure that no scans were interrupted during that merge. Releasing all the exclusive locks in the event of failure to obtain any of the locks will eliminate the possibility of creating deadlocks. After N retries, VACCUUM could abort leaving the merge to be picked up in another future VACUUM run. I would think that this would be fairly easy to implement and that except for very heavily scanned indexes, most of the time the locks could be acquired and the merge would take place without causing any concurrency problems. - Curtis ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org