oops, you're right.

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__i d 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__i d on vehicle_unit_data_200407 that caused the deadlock.


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


"Mikael Carneholm" <Mikael.Carneholm@WirelessCar.com> writes:
> Unfortunately, relfilenodes 68950 and 68122 don't exist anymore,

You should be looking at pg_class.oid, not relfilenode.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Mikael Carneholm" <Mikael.Carneholm@WirelessCar.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__i d 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__i d 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match