Re: Autovacuum deadlock - bug or not?

>Hmm, the CLUSTER on vehicle_unit_data_200407 wouldn't have taken any
>lock on vehicle_unit_data_200301. Were you perhaps issuing a series
>of CLUSTERs inside a transaction block? That would pile up exclusive
>locks on all the tables involved, which is certainly deadlock-prone.

Ah, that explains it...the clustering is done from a function that's clustering all (child) tables of a parent, and as I've come to understand, all functions have a built-in transaction (the "Clustering idx_foo on bar" notice message also originates from that function)

I'm pretty sure though that 15865 was the autovacuum process, but I'll have to run the function with autovacuum turned on before I can verify that this is (was) the case.

/Mikael

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: den 17 november 2005 17:42
To: Mikael Carneholm
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] Autovacuum deadlock - bug or not?

"Mikael Carneholm" <Mikael(dot)Carneholm(at)WirelessCar(dot)com> writes:
> dfol=> select pgc.oid, pgc.relname from pg_class pgc where pgc.oid in (68950, 68122);
> oid | relname
> -------+--------------------------
> 68950 | vehicle_unit_data_200407
> 68122 | vehicle_unit_data_200301

> NOTICE: Clustering idx_vehicle_unit_data_200407_person_information__id on vehicle_unit_data_200407
> ERROR: deadlock detected
> DETAIL: Process 29022 waits for AccessExclusiveLock on relation 68950 of database 16390; blocked by process 15865.
> Process 15865 waits for AccessShareLock on relation 68122 of database 16390; blocked by process 29022.

> So it seems that it was the clustering of idx_vehicle_unit_data_200407_person_information__id on vehicle_unit_data_200407 that caused the deadlock.

Hmm, the CLUSTER on vehicle_unit_data_200407 wouldn't have taken any
lock on vehicle_unit_data_200301. Were you perhaps issuing a series
of CLUSTERs inside a transaction block? That would pile up exclusive
locks on all the tables involved, which is certainly deadlock-prone.

I'm also wondering where that NOTICE "Clustering ..." came from, because
there is no such message anywhere in the 8.1 PG sources. You *sure*
this is 8.1?

There's something funny about 15865 too; you said that was an autovacuum
process but I don't think so. VACUUM doesn't take AccessShareLock;
there's a different lock type that that tries to acquire. And it
doesn't take any locks at all on more than one user table at a time.

regards, tom lane