Re: deadlock while re-indexing table

On 12-Feb-08, at 1:02 PM, Gregory Stark wrote:

> "Dave Cramer" <pg@fastcrypt.com> writes:
>
>> On 12-Feb-08, at 10:37 AM, Tom Lane wrote:
>>
>>> Alvaro Herrera <alvherre@commandprompt.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?
>
It would appear that the insert is running before the reindex starts.
Other than that I can't tell yet what was done in other transactions.

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