Re: deadlock while re-indexing table

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

> On 12-Feb-08, at 10:37 AM, Tom Lane wrote:
>
>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>>> Dave Cramer wrote:
>>>> 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.
>>
>>> I don't find this very surprising ... I would suggest using "reindex
>>> index" for each index instead. I'm not sure if REINDEX TABLE is
>>> supposed to be deadlock-free.
>>
>> It's not guaranteed to be so, but I'd think simple cases would be
>> okay. What's that other process doing?
>>
> The other process is inserting into the user_profile table.

Hm. This shouldn't be enough to cause a deadlock. Both inserts and reindex use
the same method to get the list of indexes which returns them in the same
order.

Did either transaction do anything else in the same transaction previously?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!