REINDEX/SELECT deadlock?

Hi,

I just noticed what looks like a deadlock situation on postgresql 8.2.4. After more than an hour of running REINDEX, two
processes are each in a "waiting" state and yet have no time used. This is also the first time I've seen this condition after
some 48 hours of continuous load testing.

The two postgresql processes that seem to be stuck on one another are:

1 S postgres 31812 22455 0 80 0 - 274059 semtim 12:13 ? 00:00:00 postgres: metacarta metacarta 127.0.0.1(54297) SELECT
waiting
1 S postgres 32577 22455 0 80 0 - 274063 semtim 12:16 ? 00:00:00 postgres: metacarta metacarta 127.0.0.1(60377)
REINDEX waiting

The rest of the postgresql processes change; here's a snapshot. (Note that process 14349 is active and continues to
intermittently run queries generated by another thread, but should not be blocking anything.) Unfortunately, I cannot tell you
exactly what the deadlocked SELECT query is, but it is likely to be against the same table that the REINDEX command has been
issued for.

1 S postgres 2046 22455 0 80 0 - 274059 - 12:23 ? 00:00:00 postgres: metacarta metacarta 127.0.0.1(33940) idle

1 S postgres 2097 22455 0 80 0 - 274059 - 12:24 ? 00:00:00 postgres: metacarta metacarta 127.0.0.1(36947) idle

0 S root 2380 4263 0 80 0 - 411 stext 12:24 pts/1 00:00:00 grep postgres:
1 R postgres 14349 22455 26 80 0 - 274385 - 11:51 ? 00:08:47 postgres: metacarta metacarta 127.0.0.1(52448) PARSE

1 S postgres 22457 22455 0 80 0 - 273864 - Jul10 ? 00:01:55 postgres: writer process

1 S postgres 22458 22455 0 80 0 - 2380 434161 Jul10 ? 00:00:50 postgres: stats collector process

Does anyone know what may be going on here? Has this been fixed on later versions of postgresql?

Thanks,
Karl

Karl Wright <kwright(at)metacarta(dot)com> writes:
> I just noticed what looks like a deadlock situation on postgresql
> 8.2.4.

Did you look into pg_locks to see what locks those transactions have and
are waiting for?

regards, tom lane

Tom Lane wrote:
> Karl Wright <kwright(at)metacarta(dot)com> writes:
>
>>I just noticed what looks like a deadlock situation on postgresql
>>8.2.4.
>
>
> Did you look into pg_locks to see what locks those transactions have and
> are waiting for?
>
> regards, tom lane

No. Unlike a typical transaction-based deadlock, I did not see a deadlock error come back - it just
seemed to wait indefinitely instead. So I'm not even sure anything will show up in the pg_locks
table. In any case, it will have to happen again before I can spelunk more, since I had to reset
everything because the system was dead and it was needed by others.

Karl