Re: First steps with 8.3 and autovacuum launcher

On Thu, 2007-10-04 at 17:33 -0400, Alvaro Herrera wrote:
> Simon Riggs escribió:
> 
> > Seems like we don't need to mess with the deadlock checker itself.
> > 
> > We can rely on the process at the head of the lock wait queue to sort
> > this out for us. So all we need do is look at the isAutovacuum flag on
> > the process that is holding the lock we're waiting on. If it isn't an
> > autoANALYZE we can carry on with the main deadlock check. We just need a
> > new kind of deadlock state to handle this, then let ProcSleep send
> > SIGINT to the autoANALYZE and then go back to sleep, waiting to be
> > reawoken when the auotANALYZE aborts.
> 
> Ok, I think this makes sense.
> 
> I can offer the following patch -- it makes it possible to determine
> whether an autovacuum process is doing analyze or not, by comparing the
> PGPROC of the running WorkerInfo list (the list has at most
> max_autovacuum_workers entries, so this is better than trolling
> ProcGlobal).

OK, I've got this working now. It successfully handles this test case,
which trips up on an auto ANALYZE every time I run it.

--
drop table a;

create table a as select generate_series(1,1000000)::integer as col1;

alter table a alter column col1 type bigint;
alter table a alter column col1 type bigint;
alter table a alter column col1 type bigint;
alter table a alter column col1 type bigint;
alter table a alter column col1 type bigint;
--

I think there may be a cleaner implementation, so I'll clean it up and
post tomorrow.

Few thoughts:

Why do we run all of the ANALYZEs in a single big transaction? That
seems like it could be the cause of many problems. ANALYZE specifically
holds locks until EOXact, so I'd recommend we start a new transaction
for each one. What do you think? 

I notice when we cancel an AV worker it always says "cancelling
autovacuum of table", even when its just an ANALYZE. Wasn't important
before but now looks a little strange.

If you want to commit this patch, I'll layer mine over the top.

Any other input anyone?

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com