Re: Problem with 2 avcuums in parallel

I wrote:
> Denis Perchine <dyp@perchine.com> writes:
>> When I run 2 vacuum's in parallel they hangs. Both.
>> I use PostgreSQL from 7.0.x CVS (almost 7.0.3).

> Hm.  I don't see a hang, but I do see errors like

> NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.
> ERROR:  WaitOnLock: error on wakeup - Aborting this transaction

> What's curious is that neither 7.0.2 nor current seem to exhibit this
> behavior (at least, it's much easier to reproduce in REL7_0_PATCHES than
> in 7.0.2 or current).

I'm not sure why it seemed easier to reproduce this deadlock in 7.0.3;
might just be because I was using a different test database for each
version.  Anyway, this turns out to be a long-standing issue.

The problem is that VACUUM does index_open() on each index of the target
relation.  That may require reading both pg_am and pg_amop to fill in
all the index-strategy data.  Guess what happens when you're vacuuming
pg_am itself, and someone else is vacuuming pg_amop.  (pg_amproc is a
possible source of deadlock here, too.)

This might be fixable with sufficient contortions, but I think we have
more important problems to worry about than whether concurrent VACUUMs
will occasionally deadlock.  It's not really easy to fix, in any case,
since we certainly want to grab exclusive lock on the target relation
before we start to investigate its indexes.  Else the indexes could
change/disappear under us :-(
        regards, tom lane