On Tue, Nov 8, 2011 at 3:26 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> On Tue, Nov 8, 2011 at 2:26 AM, Simon Riggs <si...@2ndquadrant.com> wrote: >>> I think we need to avoid long pin hold times generally. > >> In the case of a suspended sequential scan, which is the case where >> this has most recently bitten me on a production system, it actually >> seems rather unnecessary to hold the pin for a long period of time. >> If we release the buffer pin, then someone could vacuum the buffer. > > This seems unlikely to be a productive line of thought. The only way > you could release buffer pin is if you first copied all the tuples you > need out of the page, and that seems like an unacceptable performance > hit. We should not be penalizing foreground query operations for the > benefit of background maintenance like VACUUM. (The fact that we do > an equivalent thing in btree index scans isn't an argument for doing > it here, because the tradeoffs are very different. In the index case, > the amount of data to be copied is a great deal less; the length of > time the lock would have to be held is often a great deal more; and > releasing the lock quickly gives a performance benefit for other > foreground operations, not only background maintenance.) > > It strikes me that the only case where vacuum now has to wait is where > it needs to freeze an old XID. Couldn't it do that without insisting on > exclusive access? We only need exclusive access if we're going to move > data around, but we could have a code path in vacuum that just replaces > old XIDs with FrozenXID without moving/deleting anything.
Holding buffer pins for a long time is a problem in Hot Standby also, not just vacuum. AFAIK seq scans already work page at a time for normal tables. So the issue is when we *aren't* using a seq scan, e.g. nested loops joins. Is there a way to solve that? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
