Hello,

there's really wierd trouble.
When I run 2 vacuum's in parallel they hangs. Both.
I use PostgreSQL from 7.0.x CVS (almost 7.0.3).
Any ideas? Tom?

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Tue, 31 Oct 2000 20:16:29 +0600
Denis Perchine <dyp@perchine.com>
Problem with 2 avcuums in parallel
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).  Odd... in theory we shouldn't have introduced any
new bugs in REL7_0_PATCHES that wouldn't also be in current...

			regards, tom lane

Thu, 02 Nov 2000 17:42:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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

Fri, 03 Nov 2000 12:12:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problem with 2 avcuums in parallel 