pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedREINDEX/SELECT deadlock?
3+ messages / 2 participants
[nested] [flat]
* REINDEX/SELECT deadlock?
@ 2008-07-11 16:59 Karl Wright <kwright@metacarta.com>
0 siblings, 1 reply; 3+ messages in thread
From: Karl Wright @ 2008-07-11 16:59 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: REINDEX/SELECT deadlock?
@ 2008-07-11 19:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karl Wright <kwright@metacarta.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2008-07-11 19:38 UTC (permalink / raw)
To: Karl Wright <kwright@metacarta.com>; +Cc: pgsql-performance
Karl Wright <kwright@metacarta.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: REINDEX/SELECT deadlock?
@ 2008-07-11 21:40 Karl Wright <kwright@metacarta.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Karl Wright @ 2008-07-11 21:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Karl Wright <kwright@metacarta.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-07-11 21:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-11 16:59 REINDEX/SELECT deadlock? Karl Wright <kwright@metacarta.com>
2008-07-11 19:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-11 21:40 ` Karl Wright <kwright@metacarta.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox