Re: deadlock while re-indexing table

"Dave Cramer" <pg(at)fastcrypt(dot)com> writes:

> reindex table user_profile;
> ERROR: deadlock detected
> DETAIL: Process 32450 waits for AccessExclusiveLock on relation 194689112 of
> database 163880909; blocked by process 31236.
> Process 31236 waits for AccessShareLock on relation 194689110 of database
> 163880909; blocked by process 32450.

And what was process 31236 doing?

Are you running REINDEX TABLE on the same table from two different sessions?
Or are there other transactions running which call LOCK TABLE on this table?

Also, have you done other queries in this same transaction? Or other DDL in
other transactions, especially in combination with DML earlier.

Generally what frequently causes this is upgrading locks. So for example if
you do normal DML which takes a share lock, then in the same transaction try
to do DDL against the same table which requires an exclusive lock, then you'll
be at risk of deadlocks when other transactions try to do the same thing.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!