Re: deadlock while re-indexing table

On 12-Feb-08, at 5:05 PM, Tom Lane wrote:

> Dave Cramer <pg(at)fastcrypt(dot)com> writes:
>>>> The other process is inserting into the user_profile table.
>>>
>>> Did either transaction do anything else in the same transaction
>>> previously?
>>>
>> It would appear that the insert is running before the reindex starts.
>
> That's not possible --- if it had been, the insert would already have
> RowExclusiveLock on the table, which would have blocked the reindex
> from acquiring ShareLock on the table. The reindex must already have
> that, since it's trying to acquire AccessExclusiveLock on one of the
> indexes, so there can't be any active inserts on the table.
>
> I suspect the other process must be doing a series of selects in one
> transaction that use indexes of the table in some random order, but
> that's just an educated guess at this point.
>
> BTW, what PG version is this?
>
8.2.5

Dave
>