Thread: Deadlock of REINDEX and SELECT queries in PostgresSQL 7.4
Deadlock of REINDEX and SELECT queries in PostgresSQL 7.4
Hello
We are facing a deadlock kind of issue in PostgresSQL 7.4
We have 2 databases with 3 tables each. DB contains about 250 records. We observed deadlock when 2 different clients are performing REINDEX and SELECT start their operations near simultaneously.
Client 1 performs following operations in a tight loop:-
VACCUM, ANALYZE of each table, REINDEX of each table’
Client 2 performs SELECT in one of the table in a tight loop.
Upon looking at the postgres locks, it seems like all the locks are granted to ‘REINDEX’ operation and SELECT is waiting. REINDEX never returns in this scenario. This problem occurs when Client1 and Client 2 are running simultaneously. Both in a tight loop. Once this deadlock is reached all the subsequent SELECT, RENDEX operations go into ‘waiting’ mode.
Is this a known issue? Is the REINDEX and SELECT transactions directed at postgres at same time not a valid combination?
Please provide help in resolving this issue.
Thanks and Regards
Shiv
On 09/29/11 8:49 AM, Tendulker, Shivanand G Prabhu (SSTL) wrote: > > Please provide help in resolving this issue. > 7.4 is an ancient dead end release. update to something released this century, like 8.4 or 9.0... where, btw, vacuum, analyze, and reindex are now automatic and no longer need doing manually except in very unusual situations. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Thursday, September 29, 2011 8:49:07 am Tendulker, Shivanand G Prabhu (SSTL) wrote: > Hello > > We are facing a deadlock kind of issue in PostgresSQL 7.4 > > We have 2 databases with 3 tables each. DB contains about 250 records. We > observed deadlock when 2 different clients are performing REINDEX and > SELECT start their operations near simultaneously. > > Client 1 performs following operations in a tight loop:- > VACCUM, ANALYZE of each table, REINDEX of each table' > > Client 2 performs SELECT in one of the table in a tight loop. > > Upon looking at the postgres locks, it seems like all the locks are granted > to 'REINDEX' operation and SELECT is waiting. REINDEX never returns in > this scenario. This problem occurs when Client1 and Client 2 are running > simultaneously. Both in a tight loop. Once this deadlock is reached all > the subsequent SELECT, RENDEX operations go into 'waiting' mode. > > Is this a known issue? Is the REINDEX and SELECT transactions directed at > postgres at same time not a valid combination? Yes, see here: http://www.postgresql.org/docs/7.4/static/explicit-locking.html > > Please provide help in resolving this issue. > > Thanks and Regards > Shiv -- Adrian Klaver adrian.klaver@gmail.com