Re: First steps with 8.3 and autovacuum launcher

"Gregory Stark" <stark@enterprisedb.com> writes:

> "Alvaro Herrera" <alvherre@commandprompt.com> writes:
>
>> Hmm, it looks like the race condition Heikki mentioned is the culprit.
>> We need a way to stop future analyzes from starting.  Back to the
>> drawing board ...
>
> A crazy idea I just had -- what if you roll this into the deadlock check? So
> after waiting on the lock for 1s it wakes up, finds that the holder it's
> waiting on is an autovacuum process and cancels it instead of finding no
> deadlock.

Having given this a bit of thought I think it can be made quite general:

I would assign every process a priority which could be just an unsigned
integer from 0..255 stored in PGPROC. By default each process gets 128 except
autovacuum which sets its priority to 64 and for safety I would set bgwriter
and walwriter to 255, though afaik they never acquire any user-level locks..

When DeadLockCheck traverses the waiters it's normally looking for waiters
which are blocked waiting on a lock it holds itself already. I would add that
it should also compare the priority. If any waiter ahead of itself would block
this process but has a lower priority then that should be corrected.

If the process already has acquired the lock then it should be delivered a
SIGINT. I'm not sure if that works on Windows, if not then we would have to
introduce a flag in PGPROC which would also be checked on
CHECK_FOR_INTERRUPTS.

If not then ideally I would think it should try to just push the low priority
waiter ahead of it to the back of the queue. But I'm not sure how easy that
would be. Specifically I'm not sure if we would still reliably detect real
deadlocks. It seems safest to just deliver a SIGINT in that case as well,
since in the case of autovacuum it won't matter.

This also allows for possibly having a guc which allows users to lower their
own priority. In which case their queries would be automatically killed if
they held up someone else at a normal priority.

Having a flag in PGPROC instead of delivering SIGINT is attractive for other
reasons aside from portability. It would open the door to having vacuums
(perhaps just interactive vacuums) continue but reduce or zero their
vacuum_cost_delay for example. Or perhaps they could release their analyze
locks giving up on analyzing the table but continue with the vacuuming.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com