On Sun, 2011-01-16 at 13:08 -0500, Greg Smith wrote:
> Simon Riggs wrote:
> > I'm fairly confused by this thread.
> >
>
> That's becuase you think it has something to do with cancellation, which
> it doesn't. The original report here noted a real problem but got the
> theorized cause wrong. It turns out the code that acquires a lock when
> autovacuum decides it is going to process something will wait forever
> for that lock to be obtained. It cannot be the case that other locks on
> the table are causing it to cancel, or as you say it would be visible in
> the logs. Instead the AV worker will just queue up and wait for its
> turn as long as it takes.
OK, thanks for explaining.
So my proposed solution is to set a statement_timeout on autovacuum
tasks, so that the AV does eventually get canceled, if the above
mentioned case occurs. We can scale that timeout to the size of the
table.
Now every issue is a cancelation and we can handle it the way I
suggested in my last post.
I would prefer it if we had a settable lock timeout, as suggested many
moons ago. When that was discussed before it was said there was no
difference between a statement timeout and a lock timeout, but I think
there clearly is, this case being just one example.
-- Simon Riggs http://www.2ndQuadrant.com/books/PostgreSQL Development, 24x7 Support, Training and Services