Re: Limiting setting of hint bits by read-only queries; vacuum_delay

On 24 March 2013 13:29, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Sun, Mar 24, 2013 at 11:14 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Proposal is to prevent SELECTs from causing more than N buffers from
>> being dirtied by hint bit setting and block cleanup.

> I'm guessing you mean that there would be a global variable somewhere
> (why not a field in the executor node for the sequential scan?)
> counting how many blocks were marked dirty by the current plan (why
> not the whole transaction?) and once it reaches the limit subsequent
> blocks would be processed normally including setting hint bits but
> wouldn't mark the block dirty?

Yep, that's what I'm proposing.

> This means that there's no limit to the number of times an in-doubt
> record would have to have its xmin/xmax looked up in the clog. Every
> select would have to do the same work over and over again
> indefinitely.

> The whole point of the hint bits is to guarantee that each update
> causes a bounded amount of work. Each update transaction currently
> causes one write, followed by a limited period when the transaction
> status needs to be checked, followed by another write with the hint
> bit set. And that's the total work caused. After that the record can
> be read without consulting the clog or anything else.

...and as a result, the rest of your comments don't apply at all to
the proposal. Sorry about that confusion.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services