On Tue, Jun 21, 2016 at 3:29 PM, Amit Kapila <amit.kapil...@gmail.com> wrote: > On Tue, Jun 21, 2016 at 1:03 AM, Andres Freund <and...@anarazel.de> wrote: >> Well, I think generally nobody seriously looked at actually refactoring >> heap_update(), even though that'd be a good idea. But in this instance, >> the problem seems relatively fundamental: >> >> We need to lock the origin page, to do visibility checks, etc. Then we >> need to figure out the target page. Even disregarding toasting - which >> we could be doing earlier with some refactoring - we're going to have to >> release the page level lock, to lock them in ascending order. Otherwise >> we'll risk kinda likely deadlocks. > > Can we consider to use some strategy to avoid deadlocks without releasing > the lock on old page? Consider if we could have a mechanism such that > RelationGetBufferForTuple() will ensure that it always returns a new buffer > which has targetblock greater than the old block (on which we already held a > lock). I think here tricky part is whether we can get anything like that > from FSM. Also, there could be cases where we need to extend the heap when > there were pages in heap with space available, but we have ignored them > because there block number is smaller than the block number on which we have > lock.
Doesn't that mean that over time, given a workload that does only or mostly updates, your records tend to migrate further and further away from the start of the file, leaving a growing unusable space at the beginning, until you eventually need to CLUSTER/VACUUM FULL? I was wondering about speculatively asking for a free page with a lower block number than the origin page, if one is available, before locking the origin page. Then after locking the origin page, if it turns out you need a page but didn't get it earlier, asking for a free page with a higher block number than the origin page. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
